SQL Server Interview questions

This section will contain SQL Server Interview Questions and Answers.


1. How to optimize stored procedures in SQL Server?
Ans: I. Use where clause
II. Select only required fields
III. Do join on indexed key fields

2. What is the difference between Stored procedure and User defined functions?

3. Why should we go for Stored Procedures? Why not direct queries?
Ans: SP are precompiled and contain an execution plan with it. Hence, they are faster.


4. How many NULL values we can have in a Unique key field in SQL Server?
Ans: Only one. In case of Oracle, we can have multiple NULL values in a Unique key field.

5. What is correlated subquery?
6. What is an index. What are the types?

Indexes in databases are very much similar to Indexes in Books. Indexes help in searching data faster.

Types: Clustered Index
Non-Clustered Index

7. What is the difference between a clustered index and a non-clustered index?

Clustered:
1 Only one clustered index allowed per table
2 Physically rearranges the data
3 For use on columns that are frequently searched for range of data

Non-clustered:
1 Upto 249 non-clustered index allowed per table
2 Doesn’t rearrange the data. Keeps a pointer to the data.
3 For use on columns that are searched for single value.

8. What is a join? What are the types of joins?

Joins are used to retrieve data from multiple tables.

Following are the types of joins:
Inner join
Left outer join
Right outer join
Full outer join
Cross join
9. What is a transaction?

A SQL transaction is a sequence of operations performed as a
single unit of work. If all the tasks are completed successfully, then the transaction is committed (saved). If a single task fails, the transaction is rolled back (discarded).

10. What is ACID property of transaction?

A SQL transaction must exhibit ACID property, i.e Atomicity, Consistency, Isolation, and Durability.

Atomicity: A transaction is always treated as a single unit of work, i.e. either all the tasks are performed or none of them, no intermediate stage.
Consistency: When a transaction is completed, it must leave all datain a consistent state. Isolation: Modifications made by a transaction must be isolated from the modifications made by other transactions. Durability: After a transaction is completed, it’s effects are permanently in place in the system.

11. What is SET NOCOUNT ON?

When we perform a SELECT, INSERT, UPDATE or DELETE query, it returns a COUNT (number of rows affected) when SET NOCOUNT OFF. If SET NOCOUNT ON, it doesn’t return the COUNT.

12. How to delete exactly duplicate records from a table?

There are many ways. Simplest answer is:
i. Let the table tab1 contains duplicate records.
ii. Insert distinct records from tab1 in a temporary table #temp
INSERT INTO #temp
SELECT DISTINCT * FROM tab1
iii. Delete all rows from original table tab1
DELETE FROM tab1
iv. Insert from temporary table
INSERT INTO tab1
SELECT * FROM #temp
Try other solutions yourself.

13. How to get nth highest salary from employee table.

The query below demonstrates how to find the 5th highest salary. Replace 5 with any integer to get nth salary.

SELECT TOP 1 SALARY
FROM (SELECT DISTINCT TOP 5 SALARY
FROM EMPLOYEE ORDER BY SALARY DESC) a
ORDER BY SALARY ASC

Please post your comments here. Also, post your questions to get an answer.