In this article I will share around
175+ quality questions that covers all area In SQL server.
Prepare following question I am sure
those 100 questions definitely help you to crack interview in SQL server, happy
job searching friends.
1. Explain relational data?
2. What is RDBMS?
3. What are the Properties of the
4. Explain Normalization and
5. Why De-normalization required?
6. What are the different ACID
7. What are the Different Normalization
8. Find nth highest salary or get nth highest
Answer at http://www.codechef4u.com/post/2015/07/13/Find-nth-highest-salary-or-get-nth-highest-salary
9. Find nth lowest salary or get nth lowest
In this I will explain how to find nth
lowest salary of the employee in SQL Server.
Following examples explains step by
step to find lowest salary from employee table.
10. What is SET NOCOUNT ON and What is SET
When we create stored procedure there
is default sql stetement exists that is SET NOCOUNT ON, what is that.
SET NOCOUNT ON:
Stop creating a message that shows
number of rows affected by a Transact-SQL statement
or stored procedure from being
returned as part of the result set.
SET NOCOUNT OFF:
When SET NOCOUNT is OFF, the count is
11. Data table as parameter in SQL
12. How to remove duplicate rows from
13. How to remove duplicate rows from
table except one?
14. Insert multiple rows with a single INSERT
Before SQL server 2008 you require to
use INSERT INTO multiple times.
In SQL Server 2008 you can insert
multiple rows using a single SQL INSERT statement.
Example(SQL server 2008+):
INSERT INTO Products
( 'Mi-IN-T001','Kendre tab mid range' ,NULL,0),
( 'Mi-IN-T002','Kendre tab Large range' ,NULL,1),
('Mi-IN-LD03','Kendre tab Normal range' ,NULL,1)
15. Check if table exists in Sql
16. How to return the date part only
from a SQL Server datetime datatype?
17. Update from select query in sql
18. Explain Insert into select
19. Explain table valued parameters in
sql server? Why TVP used?
20. What is a Linked Server?
21. What is a Cursor?
22. What is Collation?
23. What is an Identity?
24. What is Difference between Table
Aliases and Column Aliases? Do they Affect Performance?
25. What is the difference between
CHAR and VARCHAR Datatypes?
26. What is the Difference between
VARCHAR and VARCHAR(MAX) Datatypes?
27. What is the Difference between
VARCHAR and NVARCHAR datatypes?
28. Is there any Performance
Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1
29. What is Difference in Performance
between INSERT TOP (N) INTO Table and Using Top with INSERT?
30. What is table join?
31. What are Different Types of table
32. What is inner join?
33. What is outer join?
34. What is cross join?
35. What is self join?
36. Explain left outer join and right
37. What is full outer join?
38. Equi join and non equi join is
possible with sql server?
39. Inner vs outer joins?
40. What is a Stored Procedure?
Stored procedure is a set of
Transact-SQL statements compiled into a single execution plan. You can use
stored procedure with input and output parameters.
42. What are the Different Types of Stored
There are basically two types of
· System stored procedures
· User defined stored procedures.
43. What is User defined stored procedures?
A user-defined procedure is stored
procedures created by user on user-defined database or in all system databases
except the Resource database.
44. What is System stored procedures?
System stored procedures are sql server
inbuilt stored procedures. All system stored procedures start with prefix _sp.
45. What is Temporary Stored procedure?
Temporary stored procedures are user
defined stored procedures stored in tempdb. Two types of temporary stored
procedure Global and local.
46. Extended User-Defined?
Extended procedures enable creating
external routines in a programming language such as C. These procedures are
DLLs that an instance of SQL Server can dynamically load and run.
47. Explain some Stored Procedure
creating best practices or guidelines?
48. Can a Stored Procedure call itself
or a Recursive Stored Procedure? How many levels of SP nesting is possible?
49. How to Optimize Stored Procedure Optimization?
50. How to Recompile Stored Procedure
at Run Time?
51. Find columns used in stored
52. What is a View?
53. What are Different Types of views?
54. Explain System views?
55. Explain User defined views?
56. What is indexed view?
57. What is Catalog Views?
58. How to Find Tables without
59. What is the Maximum Number of
Index per Table?
60. What is a Table Called, if it has
neither Cluster nor Non-cluster Index? What is it Used for?
61. What are the Difference between
Clustered and a Non-clustered Index?
62. What are the Different Index
Configurations a Table can have?
63. What are Various Limitations of
64. What are partitioned views and
distributed partitioned views?
65. What functions can a view be used
66. Describe the functionalities that
67. Explain Indexed views and partitioned
view with their syntax?
68. What are the restrictions that
views have to follow?
69. Explain Trigger and trigger types?
70. What are DDL Triggers and types of
71. What are DML triggers and types of
72. What is Transact-SQL DDL Trigger?
A special type of Transact-SQL stored
procedure that executes one or more Transact-SQL statements in response to a
server-scoped or database-scoped event. For example, a DDL Trigger may fire if
a statement such as ALTER SERVER CONFIGURATION is executed or if a table is
deleted by using DROP TABLE.
73. What is CLR DDL Trigger?
Instead of executing a Transact-SQL
stored procedure, a CLR trigger executes one or more methods written in managed
code that are members of an assembly created in the .NET Framework and uploaded
in SQL Server.
CREATE TRIGGER safety
FOR DROP_TABLE, ALTER_TABLE
'You must disable Trigger "safety" to drop or
74. What is AFTER DML trigger?
AFTER triggers are executed after the
action of the INSERT, UPDATE, MERGE, or DELETE statement is performed
75. What is INSTEAD OF DML trigger?
DML trigger is executed instead of the
triggering SQL statement, therefore, overriding the actions of the triggering
statements. Therefore, they can be used to perform error or value checking on
one or more columns and the perform additional actions before insert, updating
or deleting the row or rows.
76. What is logon trigger?
Special type of trigger fire when LOGON event of Sql Server is
raised is called lo logon trigger. You can use this trigger to audit Sql server
activities, control server sessions, such as to track login activity or limit
the number of sessions for a specific login.
77. Why the Trigger Fires Multiple
Times in Single Login?
78. Is it
possible to create trigger on views?
only InsteadOf trigger.
79. How many
triggers are possible per table?
InsteadOfTrigger and any After Triggers.
multiple after triggers are attached to sql table, how to control the order of
81. What is
the difference between For Trigger and after trigger?
82. Explain System Functions or
Built-In Functions? What are different types of System Functions?
83. Explain ranking functions?
84. Explain system Scalar Functions?
85. Explain Aggregate Functions?
86. Explain system Rowset Functions?
87. What is User-defined Functions?
What are the types of User-defined Functions that can be created?
88. What is User-defined Scalar
89. What is User-defined Inline
90. What is User-defined
Multi-Statement Table-Valued Function?
91. What is the Difference between a
Function and a Stored Procedure?
92. What is the Difference between a
Function and a trigger?
93. What is ROW_NUMBER()?
94. What is Difference between GETDATE
and SYSDATETIME in SQL Server 2008?
95. What is PIVOT and UNPIVOT?
96. What is the STUFF Function and How
Does it Differ from the REPLACE Function?
97. What are the basic functions for
master, msdb, model, tempdb and resource databases?
98. What are the Advantages of Using
99. If any Stored Procedure is
Encrypted, then can we see its definition in Activity Monitor?
Keys and constrains:
100. What is table constraint?
101. What are the Different Types of
102. What is default constraint?
103. Explain primary key and foreign
104. What is default constraint?
105. Explain PRIMARY KEY, FOREIGN KEY
and Unique key?
106. What is UNIQUE KEY Constraint?
107. What is CHECK Constraint?
108. What is NOT NULL Constraint?
109. What is the difference between
primary key and unique key?
110. What is an Index?
111. What is Clustered index
112. What is Nonclustered index
113. What is Hash Nonclustered index
114. What is Memory-optimized
115. What is Unique and xaml
116. What is Spatial Nonclustered index
117. What is Filtered Index?
118. What is Nonclustered
Index with included columns ?
119. What is Nonclustered Index on computed columns?
120. How to Enable/Disable Indexes?
121. Can we Insert Data if Clustered Index
122. How to Find Index Size for Each
Index on Table?
123. Why can there be only one
Clustered Index and not more than one?
124. What is the Difference between
Index Seek vs. Index Scan?
125. What is a Covered index?
126. How many clustered indexes there
can be on table ?
127. How many non clustered indexes
there can be on table ?
128. Disadvantages of the indexes?
129. How many columns can we include
on clustered index ?
130. Difference between clustered
index and non clustered index ?
131. Difference between uniqe index
and uniqe constraint?
132. Difference between primary key
and clustered index?
133. What is the ‘FILLFACTOR’?
134. What are Points to remember while
using the FILLFACTOR Argument?
135. What is subquery? Explain the Properties
of a Subquery?
Select Query inside another query is a
sub query, Select statement embedded in DML statement or nested in outer query.
You can use sub queries in SELECT,
INSERT, UPDATE, DELETE whenever expressions are used.
1. Sub query cannot include compute or for
2. Must include sub query in parentheses.
3. Subquery can include where ,group by,
having clauses these are optional.
4. Up to 32 levels sub query can be nested.
5. You can use order by clause only when
top clause included.
136. What are different types of subquery?
Self contained sub query
Co-related sub query
137. What is Self contained sub query?
query is completely independent and do not require any input from outer query
called self contained sub query.
138. Explain different types of Self contained sub query?
are three types of self contained sub queries
1. Self contained scalar sub query.
2. Self contained multi valued query.
3. table-valued sub query.
139. What is self contained scalar sub
140. What is self contained multi
141. What is table-valued sub query?
142. What is Co-related sub query?
143. How can we Rewrite Sub-Queries
into Simple Select Statements or with Joins?
144. What is transaction?
145. What is save transaction and save
146. Explain Transaction Isolation
levels in SQL Server?
147. What is read committed?
148. What is read UNCMMITED?
149. What is snapshot?
150. What is REPEATABLE READ?
151. What is SERIALIZABLE?
152. What are Different Types of
153. What are Pessimistic Lock and
154. What is Dirty Read?
155. What is Difference between
ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
156. What is Difference between Commit
and Rollback when Used in Transactions?
157. What is NOLOCK?
158. What is the Difference between
Update Lock and Exclusive Lock?
159. What is explicit mode in SQL
160. What is implicit mode in SQL
161. What is autocommit mode in SQL
162. What are “Unrepeatable reads”?
163. What are “Phantom rows”?
164. What are “Lost Updates”?
165. What are “Lock” hints?
166. What is a “Deadlock”?
167. What are the steps you can take
to avoid “Deadlocks”?
168. How can I know what locks are
running on which resource?
169. What is “Begin Trans”, “Commit
Tran”, “Rollback Tran” and “SaveTran”?
170. What is Concurrency?
171. How can we solve concurrency
172. What kind of problems occurs if
we do not implement proper locking strategy?
173. How will you Handle Error in SQL
174. What is RAISEERROR? What is
175. Explain Try...Catch with sql
176. Explain THROW statement in sql
178. Explain RAISERROR in sql server?
179. Explain following error
180. Explain Error and Transaction
Handling in SQL Server?
181. What is Temporary table in sql
server? Why we use temp table?
182. What is the Difference between a
Local and a Global Temporary Table?
183. What is local temp table?
184. What is global temp table?
185. How to insert stored procedure
result into Temporary Table?
General advanced interview questions:
186. What is CHECKPOINT Process in the
187.What is DataWarehousing?
188. What languages BI uses to achieve
189. What is Standby Servers? Explain
Types of Standby Servers.
190. What is the Correct Order of the
Logical Query Processing Phases?
191. Which TCP/IP port does the SQL
Server run on? How can it be Changed?
192. What are Different Types of
193. What is OLTP (Online Transaction
194. What is Difference between
DELETE and TRUNCATE Commands?
195. When is the use of
196. What is the Difference between a
HAVING clause and a WHERE clause?
197. What is Connection Pooling and
why it is Used?
198. What are the Authentication Modes
in SQL Server? How can it be Changed?
199. Which Command using Query
Analyzer will give you the Version of SQL Server and Operating System?
200. What is an SQL Server Agent?
201. What is Log Shipping?
202. Name 3 ways to get an Accurate
Count of the Number of Records in a Table?
203. What does it mean to have
QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
204. What is the difference between
UNION and UNION ALL?
205. What is B-Tree?
206. How to get @@ERROR and @@ROWCOUNT
at the Same Time?
207. What is a Scheduled Job or What
is a Scheduled Task?
208. Can SQL Servers Linked to other
Servers like Oracle?
209. What is BCP? When is it Used?
210. What Command do we Use to Rename
a db, a Table and a Column?
211. What are sp_configure Commands
and SET Commands?
212. How to Implement One-to-One,
One-to-Many and Many-to-Many Relationships while Designing Tables?
213. What is an Execution Plan? When
would you Use it? How would you View the Execution Plan?
214. Which are the Important Points to
Note when Multilanguage Data is Stored in a Table?
215. What is SQL Injection? How to
Protect Against SQL Injection Attack?
216. How to Find Out the List Schema
Name and Table Name for the Database?
217. How does Using a Separate Hard
Drive for Several Database Objects Improves Performance Right Away?
218. How to Find the List of Fixed
Hard Drive and Free Space on Server?
219. What is Difference between Line
Feed (\n) and Carriage Return (\r)?
220. Is It Possible to have Clustered
Index on Separate Drive From Original Table Location?
221. What is a Hint?
222. How to Delete Duplicate Rows?
223. How to delete duplicate rows from
table except one?
224. What is Use of @@ SPID in SQL
224. What is the Maximum Size per
Database for SQL Server Express?
225. How do We Know if Any Query is
Retrieving a Large Amount of Data or very little data?
226. What is the Difference between
GRANT and WITH GRANT while Giving Permissions to the User?
227. What is T-SQL Script to Take
Database Offline – Take Database Online
228. Does the Order of Columns in
UPDATE statements Matter?
229. Explain Few of the New Features
of SQL Server 2008 Management Studio
230. Explain IntelliSense for Query
231. Explain MultiServer Query
234. Explain Query Editor Regions
235. Explain Activity Monitors
236. What is Service Broker?
237. Where are SQL server Usernames
and Passwords Stored in the SQL server?
238. What is Policy Management?
239. What is Database Mirroring?
240. What are Sparse Columns?
241. What does TOP Operator Do?
242. What is CTE(Common table
243. What are the Advantages of Using
245. What is MERGE Statement?
245. Which are the New Data Types
Introduced in SQL SERVER 2008?
246. What are Synonyms?
247. What is Use of EXCEPT Clause?
248. What is XPath?
249. How to Rebuild the Master
250. What is the XML Datatype?
251. What is Data Compression?
252. What is Use of DBCC Commands?
253. How to Copy the Tables, Schema
and Views from one SQL Server to Another?
254. How to Copy Data from One Table
to Another Table?
255. What is a Filestream?
256. What is SQLCMD?
257. What do you mean by TABLESAMPLE?
258. What is Change Data Capture (CDC)
in SQL Server 2008?
259. How can I Track the Changes or
Identify the Latest Insert-Update-Delete from a Table?
260. What is the CPU Pressure?
261. How can I Get Data from a
Database on Another Server?
262. What is the Bookmark Lookup and
263. How can I Check that whether
Automatic Statistic Update is Enabled or not?
264. What is the Difference between Seek
Predicate and Predicate?
265. What are Basics of Policy
266. What are the Advantages of Policy
267. What are Policy Management Terms?
268. Where in MS SQL Server is ’100’
equal to ‘0’?
269. What is a ROLLUP Clause?
270. When I Delete any Data from a
Table, does the SQL Server reduce the size of that table?
271. What are Wait Types?
272. How to Stop Log File Growing too
273. What is sql server schema
compare? How we can compare two database schemas?
How we can compare two database data?