SQL Server Interview Question And Anser For Fresher And Experience Part 4

1.	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.

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

3.	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.

4.	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.

5.	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.

6.	What are some of the objects held in a database?
	§  Tables §  Columns §  Data types §  Stored procedures §  Triggers §  Rules §  Keys §  Constraints §  Defaults §  Indexes

7.	What is the difference between the HAVING clause and the WHERE clause?
	SQL Server Express applies the HAVING clause after it summarizes the data, whereas it applies the WHERE clause before it summarizes the data.

8.	Name the three types of user-defined functions?
	§  Scalar §  Inline table–valued §  Multi-statement table–valued.

9.	What is an identity column?
	Identity columns provide an autoincrementing value for a table.

10.	What component installs the OLEDB and ODBC drivers?
	The MDACs (Microsoft Data Access Components).

11.	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.

12.	What keyword do you use to assign a value to a variable?
	The SELECT keyword.

13.	What are the advantages of SQL Server 2005 Express?
	§  SQL Server 2005 Express is free and is easy to use. §  It provides the same protection and information management provided by more sophisticated versions of SQL Server. §  It is easy to install, provides rich database functionality, and sports deep integration with Visual Studio 2005.

14.	What function finds the difference between two dates?
	The DATEDIFF function.

15.	What does the RETURN statement do?
	The RETURN statement unconditionally exits a stored procedure without executing any other statements.

16.	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.

17.	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

18.	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”.

19.	What is an Identity?
	Identity is a column that automatically generates numeric values.

20.	What is a covered index?
	A covered index is a non clustered index that contains all the columns in a query.

21.	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.

22.	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.

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