TSQL INTERVIEW QUESTIONS [SQL Server Interview Questions]

Here are TSQL Interview Questions and Database Interview questions. I am going to do my best to provide a good list of real time questions.  I am not going to categories the questions depending upon topics but rather have them one after one as you never know what interviewer is going to ask you next ;). All the best with Database developer interview!

1.What is Heap?
2. What are DMVs in SQL Server and name two of them that will you use to find out long running queries or stored procedures?
3. If you need to find out the last time the job was run, which database will help you to find that information?
5. What are 2 differences between Delete and Truncate?
6. Let me know at least five differences between a Stored Procedure and a Function?
7. What is parameter sniffing in Stored Procedure? How you will overcome that?
8. Can you create a function and have dynamic SQL in it?
9. What are CROSS APPLY and OUTER APPLY in SQL Server and why do we need to use it?
10. What are Window functions in TSQL? In SQL 2012. new window functions are added such as FIRST_VALUE, LAST_VALUE, LEAD, LAG, Explain the definition with scenario where will you use these?
13.  I heard indexes are good, can I create cluster index on multiple columns or cluster index can be created only on one column?
14.  I need to create non-cluster index on 20 columns, data type of each column is Char (7000), can I include all of the columns in non-cluster index? What is max number of columns that I can include in non-cluster index, what other options I have if there is limit on number of columns?
25.  I want to audit information such as who created table, who has dropped table, what are my options?
27. We have an application that is running on SQL Server 2005. Our company has decided to have no more SQL server 2005 but only SQL Server 2012. We have migrated the Database to SQL Server 2012. How would I set the compatibility to SQL Server 2005 so application can run without any issues?  (Video Demo)
30.  How do you handle exceptions in TSQL?
31. List at least 5 system functions those you have used or will use to Retrieve Error information?
32. What is uncommittable state?
34. What are the things will you look into when you do performance tuning of TSQL query?
35. What is the difference between Rollup and Cube, What are alternative of these in SQL Server 2012?
36.     I have sale column in one of the table, Explain two different queries those you can use to have running total in your final output?
38.     Have you heard Wait for, Break, Continue key words in TSQL , explain about each of them.
39.     If you have created a table with identity (1, 1), is that possible that sequence can break and if yes then in which situation that happen?
40.     What are common table expressions (CTE)? what is Recursive CTE? In which situation have you used it? If you want to limit recursion how can you do that in CTE?
41.     What type of parameters you can provide to Stored Procedure? Explain where did you used them and why?
42.  What string functions have you used, if you need to find the starting position of character or string which function will you use?
45.     Can you insert ordered data into a SQL Server table and will SQL Server store data in ordered you have inserted? And if you will write Select Query to extract that data, will you retrieve in order the way you have inserted or not?
46.     Explain a scenario where do you need to write Correlated Subquery?
47.     When do you like to create filter index, do they take less or more space than cluster index on Disk? What are advantages of using Filter index?
48.     Sometime when you run your query you receive this warning with result sets as well “Warning: Null value is eliminated by an aggregate or other SET operation”. How can you avoid this?
49.     If you need to find the difference in days between two dates which function will you use?
50.     Can you use having clause without group by?
51.     Let’s say you have tableA on SERVERA and ServerB. If you want to see all the records from TableA on both servers, how will you be able to do that?
52.     There is an Excel file siting on your Desktop. You want to write a query to read that file and see the records in SSMS. How will you do that in TSQL?
53.     You have few word documents those you want to save in SQL Table, What data type will you use to save the word document in SQL Table?
54.     Let’s say you have used dynamic SQL, you have created a temp table inside Dynamic SQL , Will you be able to read data from that temp table after execution of dynamic SQL?
55. What keyword will you use to retrieve unique rows from table?
56.  Can I execute Stored Procedure inside Function?
57.  What is for xml, in which scenario have you used?
58.  Can you use Case Statement in Order by Clause?
59.  Which data type will you use that is time zone aware?
60.  What is Sparse Column?
61.  What are different ways to run dynamic SQL? If I need to pass parameter(s) to dynamic SQL, how can I do that?
62.  Cross join gives you Cartesian product, where have you used Cross Join?
63.  If I need to add “Total” Line at the end of result set, what option do I have?
65. You have built a big dynamic SQL query. The total length of this built query is more than 20,000 characters, now you want to see if your built query looks good, how will you print that query?
66.     How will you transpose column into row by using TSQL?
68.  Can you declare variable in Create View Statement if not then what object you prefer to create so you can use Select statement like view?
69.  What is Self-Join; explain a scenario where you need to use that?
70. What are the steps to create partitioned table? what are the benefits of creating it?
71.  What is Switch Operator in TSQL?
72.  If you need to get month name, which TSQL Function will you use?
73.  If you need to get Year which function will you use?
74.  If you need to extract data from Oracle table and join with SQL table, what steps are required? Explain sample query as well?
75. What new features are available in SSMS those were not in previous versions? Any third party tools those can be added in to SSMS and are helpful for daily use?
76.  Can you save query results to flat file from SSMS? (Video Demo)
77.  What are partitioned views and where we need to create these views?
78.  To connect to SQL Azure, do you need any different tool or can you use SSMS?
79. What is Key Lookup operator (bookmark lookup) and how can you remove that?
80. What is estimated Execution plan?  How can we display execution plan in text?
81. What is plan cache and how does it relate to Execution plan?
82. Which is better Index Scan or Index Seek? In which scenario Scan is better than Seek?
83.     What type of Trigger(s) can be created on Views?
86.     What is Computed column and have you used it? Are there any disadvantages of using computed column?
87.  Is ORDER BY clause valid in views, Inline functions, Derived tables, Sub queries and common table expressions?
88.     I want to create view with order by clause by any means, how can I do that?
91.     When should you run UPDATE STATISTICS?
92.     To see the total space taken by database which query will you run?
93.     What is data compression? What level of compression is available?  Should we enable on OLTP tables or DWH Tables to get benefit of this feature?
97.     If you add a new column in CDC enabled table, Will it be automatically included in CDC?
98.     If you need to find out, CDC is enabled on Database, how will you do that?
100. What are SQL Constraints and which one have you used often? Explain with scenarios where do you need them?
102. If you need to save more than two 2 GB file in SQL Server Table, which data type will you use?
103. What is the lowest level of transaction lock Table, Row or Field?
104. Can we create Unique Constraint on more than one columns?
105. Can we create more than one Unique Constraint on a table?
106. Can you use these functions in SQL Server and what will they return {fn NOW()},{fn CURRENT_TIMESTAMP()}. In TSQL they are equivalent to which functions?
107. What is the difference between Convert () and format () function?
108. Can you create Primary key Constraint on a column that has one Null value?
109. Can you create Unique Constraint on a column that has one Null value?
110. We can create only one cluster index per table, Is this statement true?
111. What is the difference between a trigger and Stored Procedure?
117. What are different types of cursors available in SQL Server? Which one do you use often? (Video Demo: Playlist contains Types of Cursors)
118. If you do not want to write your code by using cursor, what alternatives do you have in TSQL?
120. What is Columnstore Index? Should we consider using Columnstore indexes in Data Warehousing or in OLTP database?
121. To generate manual checkpoint, which TSQL statement will you use?
122. You are doing performance tuning on your query, can you display  Estimated Execution plan and Actual Execution plan together?
123. Can estimated execution plan be different from Actual execution plan?  And if yes what is the reason behind that?
124. What is the difference between DMV and DMF?
125. Which system table or DMV can you use to find out all the tables those do not have cluster index?
126. What are the benefits of using sp_executesql over EXEC?
127. What is Federated database?
128. Can I update the column on which I have created column store index?
129. What is Sql Variant data type?

130.  Can you use ranking functions without using Order by clause?
131. What is In-Memory OLTP?
132. Where will indexes exist in In-Memory OLTP?
133. Where will data exists in In-Memory OLTP?
134.  What is multi-version optimistic concurrency control in In-Memory OLTP?
135.  Do we have to have all tables of database in in memory for In-Memory OLTP or we can choose few out of all tables?
136. What is Memory Optimization Data FileGroup?
137. Can we write a query to extract records from In-Memory Table and Table which is stored on Disk?
138. What is Non-Durable Table? Do Non-durable tables use transaction log? Are they recoverable or not?
139. Can you create cluster index on memory optimized table (In-Memory OLTP Table)?

140. Does In-Memory OLTP remove lock and latch contention?
141. Which System view can you use to find out if a column exists in a database? (Video Demo)
142. If you need to find out that when an object was modified last time, which system table will you use? (Video Demo)
143.You need to add multiple columns to already existing table, Can you do in one statement or you have to write multiple statement?  (Video Demo)
144. A developer has written all the Stored procedure by using Full qualified name. You have requested to restored the database to DEV machine with different name from production. You start getting errors when tried to execute Stored procedures and you have figured out that you have to remove database name from each of Stored Procedure definition. How would to perform this task?
145. You need to add a column such as ModifiedBy to all the tables in a database, How would you do that? (Video Demo)
146. Explain a scenario, Where do you have used Merge Statement?
147. Explain a situation, Where have you used dynamic SQL?
148. What is the difference between Rank() and Dense Rank()
149. What is the difference between Raiserror and Throw?
150. What is Choose() function in SQL Server 2012? (Video Demo)
151. What type of commands are available in TSQL?
152. What is Logical Query Processing Order in SQL Server?
153. What are Static Type of Cursors in SQL Server? ( Video Demo)
154. What is Dynamic Type of Cursor in SQL Server?  ( Video Demo)
155. What is Forward Only Static Cursor in SQL Server?  ( Video Demo)
156. What is Forward Only Dynamic Cursor in SQL Server? (Video Demo)
157- What is KEYSET Cursor in SQL Server?   (Video Demo)
158- What is the difference between LOCAL AND GLOBAL Cursor in SQL Server?  (Video Demo)

How to Questions:

  1. How would you switch the database inside the Stored Procedure?
  2. How would you reseed identity column in SQL Server Table?
  3. How would you determine if the expression is Numeric?
  4. How does IN Clause affect performance?
  5. How would you find top record from a SQL Server table?
  6. How would you get the last record from a SQL Server table?
  7. How would you optimize a query or Stored Procedure?
  8. How would you concatenate string and integer value in TSQL?  (Video Demo)
  9. How would you import Image from file system to SQL Server Table by using TSQL?
  10. How would you select some random records from a SQL Server Table?
  11. How would you store query results to a file automatically? (Video Demo)
  12. How would you get the list of all local and global temp tables in SQL Server?
  13. How would you get the size of a SQL Server Table?
  14. How would you find out if the table is used by a Stored Procedure? (Video Demo)
  15. How would you get the list of all SQL Server Agent Jobs?  (Video Demo)
  16. How would you find the version of SQL Server?  (Video Demo)
  17. How would you get Month Name from Date in SQL Server?
  18. How would you remove special characters such as $,#,@ from a SQL Server Table Column?
  19. How would you find running queries on SQL Server?
  20. How would you find open Transactions on SQL Server?
  21. How would you find if process is blocked by another process?
  22. How would you disable a trigger in SQL Server? (Video Demo)
  23. How would you import data to a SQL Server table from file?
  24. How would you refresh view to update the metadata?   (Video Demo)
  25. How would you debug Stored Procedure in SQL Server Management Studio?
  26. How would you get time from datetime column values?
  27. How would you get list of all user databases in SQL Server? (Video Demo)
  28. How would you prevent SQL Injection?
  29. How would you replace Null values with 'Unknow'? (Video Demo)
  30. How would you transpose columns to rows in TSQL?
  31. How would you split comma delimited string (e.g value1,value2,value3....) into a table?
  32. How would you transpose rows into columns in TSQL?
  33. How would you prevent Parameter Sniffing?
  34. How would you read uncommitted data in SQL Server?  (Video Demo)
  35. How would you do Fuzzy matching using TSQL?
  36. How would you generate new GUID (unique-identifier) in SQL Server?
  37. How would you set Transaction Isolation Level for a session in SQL Server?
  38. How would you send an email from SQL Server?  (Video Demo)
  39. How would you truncate all the tables in a Database in SQL Server? (Video Demo)
  40. How would you drop identity property of a column? (Video Demo)
  41. How would you execute .sql file form command line?
  42. How would you check if column exists for a table or view in Database? 
  43. How would you get the list of tables with triggers created on them?  (Demo Video)
  44. How to Drop/Delete all the triggers in a SQL Server Database in SQL Server?   (Demo Video)
  45. How to get list of all disabled/enabled triggers in SQL Server Database?  (Video Demo)
  46. How to disable all the triggers in a SQL Server Database in SQL Server?  (Video Demo)
  47. How to Enable all disabled Triggers in a SQL Server Database?   (Video Demo)
  48. How would you add a new column to existing SQL Server table and populate with Default Values?   (Video Demo)
  49. How to search object such as Table/View/Stored Procedure/function etc. in all the database in SQL Server Instance?   (Video Demo)
  50. How to check if value is Numeric by using ISNUMERIC and Try_Convert Function in SQL Server?  (Video Demo)
  51. How to change Database Name inside the Stored procedure in SQL Server?  (Video Demo)
  52. What is Concat Function in TSQL and How it is different from using + for concatenation. (Video Demo)