Tricky SQL Server Interview Question and answers

Are you preparing for a SQL server interview? want some tricky questions and answers that will give you break through ? Then you are at the correct place. Here i have some bunch of tricky SQL Server interview questions and answers that will really help you to go through the interview

1.With is the Sixth normal form in SQL server? - Ohhh...You may think that, i have heard about first to forth only but what is this SIXTH? Friends, do not try to impress the interviewer just skip this question.
Sixth normal form does exist but use it when you want a relational system in conjunction with time. At this moment SQL Server does not support it directly.

2. In which Files does SQL Server Actually Store Data? - SQL server has 2 data files associated with it
1. MDF : Which is actual data file storage
2. LDF : (Log data files) which stores transaction log

3. Do you know How many locks are exist in SQL Server ? - We have these many locks exist here, see below
1. Intent
2. Shared
3. Update
4. Exclusive
5. Schema
6. Bulk Update

4.What is SQL Profiler?- It is a tool that allows administrator to monitor different events and transaction of SQL server instance, You can capture and save data about each event to a file or SQL Server table to analyze later
it is not available with EXPRESS edition

5. Can i insert into a table having just one IDENTITY column?- Yes you can, Use following Query

INSERT INTO TABLE1 DEFAULT VALUES;

6. How to drop primary key from a column using Query
- See below query to drop primary key constraint

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1_Col1

7. What is the difference between a DDL trigger and a DML trigger?- DDL commands are always deal with table structures, these command can update/modify table structure (CREATE, ALTER, DROP)
DMS commands are always deal with data/records not with table structure (INSERT, UPDATE, DELETE)

8. What the difference between UNION and UNIONALL?
- Union will remove the duplicate rows from the result set while UNIONALL does not remove them. So to avoid data redundancy you need to use UNION

9. What is the clustered and a non-clustered index?
- Clustered index is the physical index that reorders the way records in the table are physically stored, In non-clustered index logical order of the index does not match the physical stored order of the rows on disk

10. What's the difference between a primary key and a unique key?
- Both keys are not allow duplicate entries but primary key create a cluster index where as unique key create a non clustered index. Secondly primary key doesn't allow NULLs, but unique key allows one NULL only.
11. How to change the data type of a column- The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Here is the query to change column datataype
ALTER TABLE table_name ALTER COLUMN column_name data type

12. How to check the version of SQL server and operating system?
- With the help of the following query we can get it
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

13. What is CHECK Constraint?
- CHECK Constraint used to limit the values that can be placed in a column

14. How can you delete duplicate records in a table where there is no primary key?
- You can take Use of the SET ROWCOUNT command. Here if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.
15. What is NOT NULL Constraint?- This constraint force the column to not accept NULL values

16. How to copy data from one table to another table?
- With the help of 'INSERT INTO SELECT' or 'SELECT INTO' queries you can copy data from a datatable to another

17. What is PIVOT
- To automatically sort, count, and total the data stored in one table we use PIVOT. It will also rotate table as rows to columns and vice versa

18. What are DBCC commands?
- These are the Database Consistency Checker commands; They will Check disk allocation consistency, Display information about recent transactions.
19. Find the 3rd MAX salary in the emp table

Select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

20. What is the difference between Trigger and Stored Procedure?- Triggers cannot be called directly they need to associated with different quires

Finisher
Suggestion/Queries always welcome

Happy Interview
- Prasad