SQL SERVER Questions

1.	What is Sql server?
	SQL - Structured query language is the standard command set used to communicate with the relational database management system. Sql server - is commonly used as the backend system for websites and corporate CRMs and can support thousands of concurrent users.SQL Server is much more robust and scalable than a desktop database management system such as Microsoft Access.

2.

What are the System Database in Sql server 2005?

Master - Stores system level information such as user accounts, configuration settings, and info on all other databases. Model - database is used as a template for all other databases that are created Msdb - Used by the SQL Server Agent for configuring alerts and scheduled jobs etc Tempdb - Holds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.

3.	What is the difference between TRUNCATE and DELETE commands?
	TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

4.	What is OLTP?
	Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

5.	Define Normalisation?
	Normalisation is an essential part of database design. A good understanding of the semantic of data helps the designer to built efficient design using the concept of normalization.
6.	What are the difference between clustered and a non-clustered index?
	A Clustered index is a special type of index that reorders the way in which each records in the table are physically stored. A Non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

7.	What are the System Database in Sql server 2008?
	Master Model Msdb Tempdb Resource

8.	What is denormalization and when would you go for it?
	Denormalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data.Denormalization is the reverse process of normalization.

9.	What are the different types of Sub-Queries?
	Single row subquery Multiple row subquery Correralted row subquery

10.	What are constraints? Explain different types of constraints?
	Constraints is a rule or restriction concerning a piece of data that is enforced at the data level. A Constraint clause can constrain a single column or group of columns in a table. There are five types of Constraint namely Null / Not Null Primary Key Unique Check or Validation Foreign Key or References Key
11.	What are the different types of BACKUPs avaialabe in SQL Server 2005?
	In SQL Server 2005 Backup Types are Full Transaction Log Differential Partial Differential Partial File and Filegroup Copy Only Database Backups.

12.	What are Data files?
	This is the physical storage for all of the data on disk. Pages are read into the buffer cache when users request data for viewing or modification. After data has been modified in memory (the buffer cache), it is written back to the data file during the checkpoint process.

13.	Define Primary Key?
	The primary key is the columns used to uniquely identify each row of a table. A table can have only one primary key. No primary key value can appear in more than one row in the table.

14.	What is cursors? and what are the different types of cursor?
	Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. Implicit cursors Explicit cursors Paramaeteried cursors

15.	What is SQL Profiler?
	SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later
16.	Define Unique Key?
	Unique key is a one or more column that must be unique for each row of the table. It is similar to primary key. Primary key column will not accept a null. Whereas the unique key column will accept a null values.

17.	Define Joins?
	A Join combines columns and data from two or more tables (and in rare cases, of one table with itself).

18.	Define Indexes?
	Index is a general term for an Oracle/SQL features used to primarily to speed execution and imposes uniqueness upon certain data. The most important of an index is to ensure uniqueness of rows and help in speedy retrieval of data.

19.	What are the difference between primary keys and foreign keys?
	The primary key is the columns used to uniquely identify each row of a table.A table can have only one primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

20.	Define Clusters?
	Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. A cluster contains one or more tables, which have one or more column in common among them.
21.	What are the transaction properties?
	Atomicity Consistency Isolation Durability

22.	Define Synonym?
	Synonym is an alternative method to creating a view that includes the entire table or view from another user it to create a synonym. A synonym is a name assigned to a table or view that may thereafter be used to refer to it.

23.	What is an active database?
	Active database is a database that includes active rules, mostly in the form of ECA rules(Event Condition rules). Active database systems enhance traditional database functionality with powerful rule processing cabalities, providing a uniform and efficient mechanism for database system applications.

24.	What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
	HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

25.	What are the purpose of Normalisation?
	Minimize redundancy in data. Remove insert, delete and update anamoly during the database activities. Reduce the need to reorganize data it is modified or enhanced. Normalisation reduces a complex user view to a set of small and stable subgroups of fields or relations.
26.	Define Self Join?
	Self join means joining one table with itself. The self join can be viewed as a join of two copies of the same table.

27.	What the difference between UNION and UNIONALL?
	Union will remove the duplicate rows from the result set while Union all does’nt.

28.	What are different type of Collation Sensitivity?
	The different phases of transaction are : Case sensitivity Accent sensitivity Kana Sensitivity Width sensitivity

29.	What is the difference Function and Stored Procedure?
	UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF›s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

30.	What is the difference between a local and a global variable?
	A Local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement. A Global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time
31.	What is NOT NULL Constraint?
	A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

32.	What is log shipping?
	Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server.

33.	What is Cross Join?
	A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

34.	What is Self Join?
	A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table.

35.	Define Normalisation?
	Normalisation is an essential part of database design. A good understanding of the semantic of data helps the designer to built efficient design using the concept of normalization.
36.	What is the difference between Triggers and Stored Procedure?
	Stored Procedures are called by the programmer wherever it wants to fire but triggers fired automatically when insert,delete,update occured. And triggers can be implemented to tables & views only where as stored procedure used in the database independently.

37.	What are the properties of Sub-Query?
	A subquery must be enclosed in the parenthesis. A subquery must be put in the right hand of the comparison operator A subquery cannot contain a ORDER-BY clause. A query can contain more than one sub-queries.

38.	Where are SQL server users names and passwords are stored in sql server?
	They get stored in master db in the sysxlogins table.

39.	What are the types of subscriptions in SQL Server replication?
	There are two types of replication in sql server are : Push Pull

40.	What does REVERT do in SQL Server 2005?
	Restores your previous execution context.If you have changed your execution context with EXECUTE AS, the REVERT statement will restore the last context prior to the EXECUTE AS.
41.	What does NULL mean?
	The value NULL means UNKNOWN; it does not mean (empty string). Assuming ANSI_NULLS are on in your SQL Server database, which they are by default, any comparison to the value NULL will yield the value NULL.

42.	What are the Different Types of Normalization?
	First Normal Form Second Normal Form Third Normal Form Boyce’s Normal Form Fourth Normal Form Fifth Normal Form

43.	What are Page Splits?
	Pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as “Page Split”.

44.	What is an Identity?
	Identity is a column that automatically generates numeric values
45.	What is the difference between SET and SELECT?
	Both SET and SELECT can be used to assign values to variables. It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. Examples declare @i int set @i=1 This is used to assign constant values. select @i=max(column_name)from table_name for ex. select @i=max(emp_id) from table_emp.

46.	What is the difference between char , varchar and nvarchar?
	char(n)Fixed length non unicode character data with length of n bytes.n must be a value from 1 through 8,000. varchar(n)variable length non unicode character data with length of n bytes. nvarchar(n)variable length unicode character data of n characters. n must be a value from 1 through 4,000.

47.	How many types of triggers are there?
	There are three types of triggers. DML Triggers --> AFTER Triggers --> INSTEAD OF Triggers DDL Triggers CLR Triggers