SQL SERVER Interview Questions – SET 1 ( 100+ Questions )

CLICK HERE (SQL SERVER Interview Questions) to download the questions in PDF format.

I have been collecting interview questions from the people who have given interviews at various organizations. Please go through these questions before attending any technical Interview. Below are the list of questions on SQL Server. Please add a comment if you have any question in mind , will add that also.

Sr No	Question
1	What are statistics? Where they are used and how to check statistics.
2	What are the types of Fragmentations
3	Can we use GUID as Primary key in a table ?
4	Difference between Unique Index vs Unique Constraint
5	What are RED Flags in SQL Server and what is there usage ?
6	What are the types of physical joins in SQL Server ? What are the different join operators in SQL Server?
7	What is a Latch in SQL Server ?
8	Difference between Latch and Lock ..
9	2014 New Features in DB Engine.
10	In which scenarios we should not use CTE’s.
11	What do you mean by Cardinality in SQL Server.
12	What are Histogram and Density Vector.
13	Why we can’t put Order by inside the view?
14	What is a cross apply and how to use this?
15	Can we use more than one CTE in a single select query?
16	I have opened a nested transaction inside an outer transaction, if i do rollback which transaction will be undone?
17	How do you know the total transaction count?
18	I have created a table variable can we use it in a nested stored procedure? If not what is the scope of a table variable?
19	If suppose we have a user defined data type. If we can modify the length of the data type does it effects in all places across the database?
20	Can we fire a trigger manually?
21	What are the magic tables? Do we have “Updated” magic table?
22	What is the difference between UnionAll and Merge?
23	Basic difference between stored procedure and user defined function?
24	See we have a simple query that’s calling a static function, like “Select * from employee where joiningdate < getstaticdate()”? Does it call function for every time or only for matched rows? How you tune this query?
25	Why should we use CTE?
26	What is the difference between sub query and correlated query
27	What is the difference between pessimistic locking and optimistic locking?
28	What is XACT_ABORT ON?
29	How to filter nested stored procedure code from profiler?
30	What happens when a rollback happens in inside a nested stored procedure?
31	What is a bitmap index?
32	How to avoid bookmark lookup in execution plan?
33	When index scan happens?
34	Does temp tables and table variables both stored in tempdb?
35	Why cursors are so costly?
36	Can we call a procedure from a function?
37	Can we write DML inside a function?
38	What is the best value for MAXDOP value?
39	Which is better “Left Outer” Or “NOT EXIST”?
40	How to find the statistics are outdated?
41	How to find the query running on a given SPID?
42	What are the limitations on “SET ROWCOUNT”?
43	While creating a “Include” columns on which basis we should consider main column and include columns?
44	How to find the last statistics update date?
45	An indexed view is referring only one base table. Both view and table are having index defined on them. Which index would be utilized when a query executed against the table.
46	I have an indexed view, now base table data has been modified, does the modified data reflected in view automatically?
47	Does “TRUNCATE” DDL or DML command?
48	I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 45000 times. How could you be able to handle the situation?
49	What is index reorganization?
50	How sql engine knows which index has to be used while dealing with indexed views?
51	How to prevent bad parameter sniffing? What exactly it means?
52	What dll file that handle the transaction logs in logshipping?
53	How to find all dependent objects of a table?
54	Which is better a CTE or a subquery? Why?
55	How to rebuild a master database? Ho to restore a master database?
56	Any alternative to triggers?
57	Top performance tuning tools
58	What events need to be added to capture execution plan in sql profiller?
59	What is the new lock escalation in sql 2008?
60	What is a filtered index?
61	What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value”
62	What is fragmentation? How it happens?
63	I have a log file which is of 250 GB. Log is full. We don’t have a disk space on any other drive for creating .ndf, auto growth is ON , and essentially there are no options to allocate new space for the file. What’s your action plan?
64	How B-Tree formed for Clustered and non clustered indexes?
65	How B-Tree forms for indexes with included column?
66	Does alzebrizer tree stores in memory for stored procedures, views and constraints?
67	How to design TempDB files? And what is the limit?
68	How VLF’s created for tempDB?
69	When the checkpoint can happen? What it exactly do?
70	When the lazywriter happens and what it’ll do?
71	What is memory grant in SQL Server?
72	Why resourceDB introduced?
73	How to move master database?
74	How to rebuild master database and what are the considerations?
75	What is a boot page?
76	What is .TUF file? What is the significance of the same? Any implications if the file is deleted?
77	What are the different shrink options?
78	How do you know whether statistics are latest or expired?​
79	How to create linked server
80	Can you give some examples for One to One, One to Many and Many to Many relationships?
81	How to capture a trace from production without any impact on performance?
82	How to capture the long running queries?
83	You have any idea on Table Partitions?
84	You have got a request to execute a query which is an “Update” query. That update is updating 5 million rows, after an hour it’s still executing and you are getting lot of requests from report users that their things are getting slow down. What’s your action plan?
85	See we have a view which is getting data from different tables. One day it’s starts executing infinitely. I have seen no blocking , no bulk operation happened. I have stopped all jobs and maintenance plans on the server. No one is connected to the database but still it’s been taking longer time. What might be the possible reasons?
86	Can you explain sql server transaction log architecture?
87	What are the phases of sql server database restore process?​
88	Do you have any idea about sparse column?
89	We have a procedure which is running fine till today afternoon. Suddenly it started taking long time to execute and there of it leads to a timeout error from application. What might be happening? How you troubleshoot?
90	What are the critical issues you faced in your career?
91	How to change the port number for SQL Server? Default port no of SQL SERVER
92	Difference between ISNULL and COAELSCE
93	Difference between partition by and patindex
94	What is the difference between CROSS / OUTER APPLY AND JOINS in T-SQL?
95	What is stuff function? Difference between stuff and replace?
96	We have a query which is running fine in development but facing performance issues at production. You got the execution plan from production DBA. Now you need to compare with the development execution plan. What is your approach?
97	What is lock Escalation
98	How do you retrieve random 5 rows from a table
99	What exactly you check in the query execution plan window?
100	What is the output of len(1234.56)
101	Where in MS SQL Server is ’100’ equal to ‘0’?
102	Explain clearly about all the normal forms with proper example
103	I have a table with millions of rows. I want to retain only last 5% of the rows ? How do u do it ? (Expectation :- transfer last 5% to temp table
104	Considerations  while designing a DB
105	Considerations while creating tables and its naming conventions
106	Explain transaction log in detail
107	I have a 500 GB Table which is performing slow ? Your plan of action ?
108	Explain Column Store Index
109	Explain Isolation Levels ? Default one ?