SQL Server Interview Questions and Answers

1.  What is an Index?

Answer:

• Index is a database object, which can be created on one or more columns.
• An index contains keys that are stored in the form of structure (B-tree) and enables SQL Server to find the row or rows associated with the key values quickly.
• It minimizes the number of data comparisons and improves the performance of data retrieval.
• Don't forget, indexes add some overhead on data modification like create, delete and modify. With each modification of data, the structure of an index reorders itself that adds overhead to the server.

Although we can create indexes on multiple columns but unnecessary indexes diminish the performance of query.

Index is stored in the form of structure (B-Tree) as shown in fig:

Video : SQL Server Interview Questions and Answers for Freshers

2. What are Clustered Indexes and Non-Clustered Indexes?

Answer:

A clustered index reorders the way records in the table are physically stored. Therefore, table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.



Example:

Select * from employee where studid = 4; (Without index it requires 4 comparison and with index it will return value at first comparison)

Select * from employee where studid = 8; (Without index it requires 8 comparison and with index it will return value at fourth comparison)

Similarly, studid = 1,048,576 will require only 20 comparisons with index.

Non-Clustered Index:

A non clustered index is an index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

A table can have more than one Non-Clustered index.

An Index of a book is a perfect example of Non-Clustered index.

3. Truncate vs. Delete

Answer:

Truncate	Delete
DDL command	DML command
Table lock	Row lock
No where clause	Where clause
Faster	Slower
Identify column is reset	Retain the identity
Can't activate a trigger	Trigger will be fired

4. Difference between a primary key and a unique key.

Answer:

Both enforce uniqueness of the column on which they are defined.

Primary key creates a clustered index on the column by default, whereas unique creates a non-clustered index by default.

Primary key doesn't allow NULLs, but unique key allows one NULL only.

There can only be one primary key in a table whereas we can have more than one unique key in one table.

5. Explain the use of UPDATE_STATISTICS command.

Answer:

UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If a large amount of deletions, any modification or Bulk Copy into the tables has occurred, UPDATE_STATISTICS command is used to update the indexes to take these changes into account.   

6. Difference between a HAVING CLAUSE and a WHERE CLAUSE.

Answer:

Both are used to specify a search condition to a query.

HAVING can be used only with the SELECT statement.

Having Clause is basically used only with the GROUP BY function in a query

WHERE Clause is applied to each row before they are part of the GROUP BY function in a query

7. What are a Local and a Global temporary table?

Answer:

Temporary table is just like Permanent tables in database except they are created in tempdb and automatically deleted when they are no longer in use.

There are 2 types of temporary tables:

• Local temporary tables
• Global temporary tables

Local temporary tables

These tables are visible only to the connection that creates it, and deleted when the connection is closed.

They accept the single hash value "#" as the prefix when created.

Syntax:

(CREATE TABLE #temp)

If the Temporary Table is created in a Stored Procedure then it is automatically dropped on the completion of the Stored Procedure execution.

Global temporary tables:

These tables are visible to all connections of SQL Server, and only destroyed when the last connection referencing the table is closed.

They accept two "##" values as the prefix at the time of their creation.

Syntax:

(CREATE TABLE ##temp).

8. Stuff VS Replace function in SQL Server

Answer:

Stuff function:

This function replaces string from the given start position with string passed as last argument.

Syntax:

STUFF ( character_expression , start , length , replaceWith_expression )

Example:

Select Stuff ('Windows', 3, 3, 'abc')

Result:

Wiabcws

Replace Function

Replace function is used to replace all occurrence of a specified with the string passed as last argument.

Syntax:

REPLACE ( string_expression , string_pattern , string_replacement )

Example:

Select Replace ('Windowswireless', 'wi', 'xy')

Result:

xyndowsxyreless