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 Relational Tables?

4. Explain Normalization and De-normalization?

5. Why De-normalization required?

6. What are the different ACID properties?

7. What are the Different Normalization Forms?

8. Find nth highest salary or get nth highest salary?

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 salary?

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 NOCOUNT OFF?

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 returned.

11. Data table as parameter in SQL server?

12. How to remove duplicate rows from table?

13. How to remove duplicate rows from table except one?

14. Insert multiple rows with a single INSERT statement?

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+):

15. Check if table exists in Sql Server?

16. How to return the date part only from a SQL Server datetime datatype?

17. Update from select query in sql server?

18. Explain Insert into select statement?

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 from table)?

29. What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?

Joins:

30. What is table join?

31. What are Different Types of table joins?

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 outer join?

37. What is full outer join?

38. Equi join and non equi join is possible with sql server?

39. Inner vs outer joins?

Stored Procedure:

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 procedures?

There are basically two types of stored procedures:

·       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 procedure?

View:

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 Indexes?

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 the Views?

64. What are partitioned views and distributed partitioned views?

65. What functions can a view be used to performed?

66. Describe the functionalities that views support?

67. Explain Indexed views and partitioned view with their syntax?

68. What are the restrictions that views have to follow?

Triggers:

69. Explain Trigger and trigger types?

70. What are DDL Triggers and types of DDL trigger?

71. What are DML triggers and types of DML triggers?

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.

Example:

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

   PRINT 'You must disable Trigger "safety" to drop or alter tables!'

   ROLLBACK;

74. What is AFTER DML trigger?

AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed successfully.

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?

Yes, only InsteadOf trigger.

79. How many triggers are possible per table?

One InsteadOfTrigger and any After Triggers.

80. When multiple after triggers are attached to sql table, how to control the order of execution?

Using sp_settriggerorder procedure.

81. What is the difference between For Trigger and after trigger?

It’s same.

Functions:

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 Function?

89. What is User-defined Inline Table-Valued Function?

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 Stored Procedures?

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 constraints?

102. What is default constraint?

103. Explain primary key and foreign key constraints?

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?

Index:

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 nonclustered indexes

115. What is Unique and xaml Nonclustered index

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 is Disabled?

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?

Subquery:

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.

Properties:

1.       Sub query cannot include compute or for browse clause.

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?

1.   Self contained sub query

2.   Co-related sub query

137. What is Self contained sub query?

Sub 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?

There 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 query?

140. What is self contained multi valued query?

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?

Transaction

144. What is transaction?

145. What is save transaction and save point?

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 Locks?

153. What are Pessimistic Lock and Optimistic Lock?

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 Server?

160. What is implicit mode in SQL Server?

161. What is autocommit mode in SQL Server?

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 problems?

172. What kind of problems occurs if we do not implement proper locking strategy?

Exception:

173. How will you Handle Error in SQL SERVER 2008?

174. What is RAISEERROR? What is RAISEERROR?

175. Explain Try...Catch with sql server?

176. Explain THROW statement in sql server 2008?

178. Explain RAISERROR in sql server?

179. Explain following error properties?

ERROR_NUMBER()

ERROR_STATE()

ERROR_SEVERITY()

ERROR_LINE()

ERROR_PROCEDURE()

ERROR_MESSAGE()

180. Explain Error and Transaction Handling in SQL Server?

Temporary table:

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 SQL Server?

187.What is DataWarehousing?

188. What languages BI uses to achieve the goal?

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 Collation Sensitivity?

193. What is OLTP (Online Transaction Processing)?

194. What is Difference between DELETE  and TRUNCATE Commands?

195. When is the use of UPDATE_STATISTICS command?

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 Server?

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 Editing

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 expression)?

243. What are the Advantages of Using CTE?

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 Database?

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 RID Lookup?

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 Management?

266. What are the Advantages of Policy Management?

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 Big?

273. What is sql server schema compare? How we can compare two database schemas?

275.  How we can compare two database data?