100 SQL Server Interview Questions

1. What is SQL Server?
2. What is Execution Plan in SQL Server? Does it execute T-SQL queries?
3. What is difference between SQL Server parser and SQL Server optimizer?
4. Is SQL Server optimizer part of SQL Server compiler?
5. What is Parameter sniffing in SQL Server?
6. What is difference between Temporary Table and Temporary Result set in SQL Server?
7. How can I create Derived Table in SQL Server?
8. What are different ways to create calculated column in T-SQL?
9. What is purpose of AS keyword in stored procedure in T-SQL?
10. How can I create a recursive CTE in T-SQL?
11. What are different forms of Case Statement in T-SQL?
12. Differentiate CASE expression and CASE statement point by point in T-SQL.
13. What are steps for Error Handling in T-SQL?
14. Is there any difference between Throw and RaiseError in T-SQL?
15. What are different ways to initialize variable in T-SQL?
16. Is there any difference in initialization of variable using SET and using SELECT in T-SQL?
17. How can I declare a table variable and initialize it in T-SQL. Give an example.
18. I have confusion about Cursor and Cursor pointer in T-SQL. Does cursor represent a result set and pointer to its active row?
19. Is it true that if a table has  10 millions records then its cursor will have all 10 millions rows in memory? So how is so much memory allocated?
20. How many rows are fetched into cursor when a cursor is declared for a table having 10 millions rows?
21. How can I avoid Dirty Read in Concurrent Transaction in T-SQL?
22. When should I use NOLOCK in a query in T-SQL?
23. Can you please give an example of Phantom Read in T-SQL?
24. List all isolation levels in T-SQL with their definitions.
25. Is isolation level set at database or at table in T-SQL?
26. How can I create an inline Temporary Table in T-SQL?
27. How can I create an multi-statement Temporary Table in T-SQL?
28. What are different types of temporary tables in T-SQL?
29. What is Trigger Logon in T-SQL?
30. How can I convert data from Rows to Columns in T-SQL?
31. Explain conditional aggregation in T-SQL.
32. What are different ways to find nth highest salary in T-SQL?
33. Give practical examples of Sequence in T-SQL.
34. How can I generate a random primary key in T-SQL?
35. Explain Dynamic SQL in SQL Server.
36. What is difference between Database owner and Database Schema in SQL Server?
37. What is difference between Login and User in SQL Server?
38. Write the STEPS to create SQL Server instance.
39. Write the STEPS to create additional SQL Server instance on machine where SQL Server default is installed.
40. Is SQL Server Agent available with LocalDb Express?
41. Please explain SQL Blocking Query in SQL Server.
42. My SQL Server name is (localdb)\mssqllocaldb. What does it mean and why slash is used?
43. How can I use Azure Data Studio for learning purpose?
44. Explain Sequence in T-SQL with its practical examples.
45. Are all values of Sequence unique in T-SQL?
46. Can I create a Sequence in SQL with numeric values concatenated with string?
47. What is difference between column Constraints  and table Constraints ?
48. What are differences between SQL server and MS Access?
49. What is difference between foreground service and background service?
50. Give examples of background service in SQL Server.
51. Discuss about master system database available in SQL server.
52. Discuss about model system database available in SQL server.
53. Discuss about tempdb system database available in SQL server.
54. Explain the concepts of Connection and Session in SQL Server.
55. What is local and global session in SQL Server?
56. How can you disable null propagation feature in SQL server?
57. How can you convert a database into single user database?
58. What is importance of filegroup in SQL Server database?
59. How can I create a filegroup in SQL Server?
60. What is DBO in SQL server?
61. What is DBO in SQL Server?
62. Explain different types of database backups in SQL Server.
63. How is batch created in T-SQL and what cautions are required in this regard?
64. What if  one of T-SQL statements in a batch throws error?
65. What if Go command is not used to batch statements and one of T-SQL statements throws error?
66. How are multiple batches in T-SQL script compiled? Are they compiled batch after batch?
67. Can we use variable of one batch into another batch in T-SQL?
68. What is shared lock? Explain it in T-SQL.
69. Is shared lock acquired automatically in T-SQL?
70. What is benefit of shared lock in T-SQL?
71. Can we disable shared lock in SQL Server?
72. What is difference between exclusive lock and shared lock?
73. When should we use explicit transaction and when implicit transaction in T-SQL?
74. What is automatic transactions in SQL Server?
75. What is linked server in SQL Server?
76. What is SQL Server Native Client?
77. What is Database Provider?
78. What is Database API?  Is Database Provider a Database API?
79. What is row level lock in SQL Server?
80. What is page level lock in SQL Server?
81. What is table level lock in SQL Server?
82. What is RID lock in SQL Server?
83. What is intent lock in SQL Server?
84. How many shared locks can be on data at a time?
85. How many exclusive locks can be on data at a time?
86. is there any difference between exclusive lock and update lock in SQL?
87. What is table hints in T-SQL. Give examples.
88. Is there any difference between table hints and query hints in T-SQL?
89. Define transaction isolation level in T-SQL.
90. Create a Customers table and insert some data into it using INSERT statements.
91. Give examples of configuration functions in SQL Server.
92. How to find connection string in T-SQL for current connection?
93. What is practical use of sysadmins in SQL Server?
94. What is INFORMATION_SCHEMA in SQL Server?
95. How can I find columns information of a table in T-SQL?
96. How can I find tables information of a database in T-SQL?
97. How can I find databases information of current SQL Server in T-SQL?
98. What is Client Network Library?
99. What is Server Network Library?
100. What is basic difference between Client Network Library and Server Network Library?