SQL Server Interview Questions

SQL server interview questions

Q1. What is the difference between a primary and unique key?
Ans1. Both primary key and unique enforce uniqueness of the column on whichthey are defined. But by default primary key creates a clustered indexon the column, where are unique creates a nonclustered index bydefault. Another major difference is that, primary key doesn't allowNULLs, but unique key allows one NULL only.

Q2. What is bit datatype and what information is stored in a bit column?
Ans2. Information stored 0, 1, Null

Q3. What is the difference between delete table and truncate table?
Ans3. DELETE TABLE is a logged operation, so the deletion of each row getslogged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of thetable, which makes it faster. TRUNCATE TABLE can be rolledback.

Q4. How to find the 6th highest salary from EMPLOYEES table?
Ans4. Select TOP 1 salary from (Select DISTINCT TOP 6 salary from EMPLOYEES order by salary desc) Order by salary.

Q5. What is a Deadlock?
Ans5. Deadlock is a situation when two processes, each having a lock on one piece of Data, attempting to acquire lock on other's piece.

Q6. What is the difference between UNION and JOINS?
Ans6. A join selects COLUMNS from 2 or more tables, while UNION selects ROWS.