MS SQL Interview Questions and Answers
1. What is a Primary Key in MS SQL?
Ans: A Primary Key is a column or a set of columns that uniquely identifies each row in a table. It enforces entity integrity by ensuring that no two rows have the same value.
2. What is the difference between `DELETE` and `TRUNCATE` in MS SQL?
Ans: `DELETE` removes rows one at a time and logs individual row deletions, allowing for rollback. `TRUNCATE` removes all rows by deallocating the data pages and is faster but cannot be rolled back.
3. What is a Foreign Key in MS SQL?
Ans: A Foreign Key is a column or a set of columns that creates a link between two tables to enforce referential integrity. It ensures that the value in one table matches values in another table.
4. What is the purpose of the `JOIN` clause in MS SQL?
Ans: The `JOIN` clause is used to combine rows from two or more tables based on a related column between them. Common types include `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, and `FULL JOIN`.
5. What is a Stored Procedure in MS SQL?
Ans: A Stored Procedure is a precompiled collection of SQL statements and declarations stored in the database. It can accept input parameters, perform operations, and return output.
6. What is the difference between `CLUSTERED` and `NONCLUSTERED` indexes?
Ans: A `CLUSTERED` index determines the physical order of data in a table. A `NONCLUSTERED` index does not alter the physical order but creates a separate object with pointers to the data.
7. What is a View in MS SQL?
Ans: A View is a virtual table based on the result set of an SQL query. It can join and consolidate data from multiple tables and present it as a single table.
8. What is the purpose of the `GROUP BY` clause in MS SQL?
Ans: The `GROUP BY` clause is used to group rows that have the same values in specified columns into aggregated data.
9. What is a Trigger in MS SQL?
Ans: A Trigger is a special kind of stored procedure that automatically executes in response to certain events on a particular table or view, such as `INSERT`, `UPDATE`, or `DELETE`.
10. What is the difference between `CHAR` and `VARCHAR` data types?
Ans: `CHAR` is a fixed-length data type that pads spaces to the specified length, while `VARCHAR` is a variable-length data type that stores only the actual characters entered.
11. What is the purpose of the `HAVING` clause in MS SQL?
Ans: The `HAVING` clause is used to filter records that are returned by the `GROUP BY` clause, similar to how `WHERE` filters rows before grouping.
12. What is a Cursor in MS SQL?
Ans: A Cursor is a database object used to retrieve and process rows from a result set one at a time.
Looking for more ASP.NET Core resources? Read our Most important ASP.NET Web Forms Interview Questions to build a strong foundation!
13. What is the purpose of the `UNION` operator in MS SQL?
Ans: The `UNION` operator combines the results of two or more `SELECT` statements, removing duplicate rows.
14. What is a Subquery in MS SQL?
Ans: A Subquery is a query nested inside a `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement, or inside another subquery.
15. What is the purpose of the `EXISTS` keyword in MS SQL?
Ans: The `EXISTS` keyword is used to test for the existence of any record in a subquery. It returns `TRUE` if the subquery returns one or more records.
16. What is the difference between `INNER JOIN` and `OUTER JOIN`?
Ans: `INNER JOIN` returns only the rows that have matching values in both tables. `OUTER JOIN` returns all rows from one table and the matched rows from the other table, including unmatched rows.
17. What is a Transaction in MS SQL?
Ans: A Transaction is a sequence of one or more SQL operations treated as a single unit of work. Transactions ensure data integrity by following the ACID properties.
18. What is the purpose of the `COMMIT` statement in MS SQL?
Ans: The `COMMIT` statement is used to save all transactions to the database since the last `COMMIT` or `ROLLBACK` command.
19. What is the purpose of the `ROLLBACK` statement in MS SQL?
Ans: The `ROLLBACK` statement is used to undo transactions that have not been saved to the database.
20. What is a Deadlock in MS SQL?
Ans: A Deadlock occurs when two or more transactions are waiting indefinitely for each other to release locks. It results in neither transaction being able to proceed.
21. What is the purpose of the `ISNULL` function in MS SQL?
Ans: The `ISNULL` function replaces `NULL` values with a specified replacement value.
22. What is the difference between `UNION` and `UNION ALL`?
Ans: `UNION` removes duplicate rows from the result set, while `UNION ALL` includes all rows, including duplicates.
23. What is a Temporary Table in MS SQL?
Ans: A Temporary Table is a short-term table that exists only within the session or the duration of a transaction. It is prefixed with `#`.
24. What is the purpose of the `COALESCE` function in MS SQL?
Ans: The `COALESCE` function returns the first non-null value in a list of expressions.
25. What is the difference between `LEFT JOIN` and `RIGHT JOIN`?
Ans: `LEFT JOIN` returns all rows from the left table and the matched rows from the right table. `RIGHT JOIN` returns all rows from the right table and the matched rows from the left table.
26. What is a Common Table Expression (CTE) in MS SQL?
Ans: A CTE is a temporary result set that can be referenced within a `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement. It is defined using the `WITH` keyword.
27. What is the purpose of the `CASE` statement in MS SQL?
Ans: The `CASE` statement allows for conditional logic within a query, similar to an `IF-THEN-ELSE` statement in programming.
28. What is the difference between `DROP` and `ALTER` commands?
Ans: `DROP` removes an object from the database, such as a table or view. `ALTER` modifies the structure of an existing database object without removing it.
29. What is a User-Defined Function (UDF) in MS SQL?
Ans: A UDF is a function provided by the user of a database. It can be used to encapsulate code for reuse and can return either scalar or table values.
30. What is the purpose of the `DISTINCT` keyword in MS SQL?
Ans: The `DISTINCT` keyword is used to return only unique values in the result set, eliminating duplicates.
31. What is the difference between `IDENTITY` and `SEQUENCE` in MS SQL?
Ans: `IDENTITY` is a column property that generates unique incremental values automatically. `SEQUENCE` is an object that generates a sequence of numeric values according to a specified definition.
32. What is the purpose of the `TOP` clause in MS SQL?
Ans: The `TOP` clause is used to specify the number or percentage of rows to return from a query.
33. What is a Constraint in MS SQL?
Ans: A Constraint is a rule enforced on data columns to ensure the accuracy and reliability of the data in the database. Examples include `PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`, `CHECK`, and `DEFAULT`.
34. What is the purpose of the `DEFAULT` constraint in MS SQL?
Ans: The `DEFAULT` constraint provides a default value for a column when no value is specified.
35. What is the difference between `CHECK` and `DEFAULT` constraints?
Ans: `CHECK` constraint ensures that all values in a column meet a specific condition. `DEFAULT` constraint provides a default value for a column when no value is specified.
36. What is the purpose of the `UNIQUE` constraint in MS SQL?
Ans: The `UNIQUE` constraint ensures that all values in a column are different.
37. What is a Schema in MS SQL?
Ans: A Schema is a collection of database objects, such as tables, views, and stored procedures, that provides a way to logically group objects in a database.
38. What is the purpose of the `ALTER TABLE` statement in MS SQL?
Ans: The `ALTER TABLE` statement is used to add, delete, or modify columns in an existing table.
39. What is the difference between `GETDATE()` and `SYSDATETIME()` functions?
Ans: `GETDATE()` returns the current date and time with an accuracy of 3 milliseconds. `SYSDATETIME()` returns the current date and time with higher precision, up to 100 nanoseconds.
40. What is the purpose of the `LIKE` operator in MS SQL?
Ans: The `LIKE` operator is used to search for a specified pattern in a column.
41. What is a Collation in MS SQL?
Ans: A Collation defines the rules for how string comparisons are evaluated in a database, including case sensitivity, accent sensitivity, and character width.
42. What is the purpose of the `CAST` and `CONVERT` functions in MS SQL?
Ans: `CAST` and `CONVERT` are used to explicitly convert a data type to another. `CONVERT` offers more formatting options compared to `CAST`.
43. What is the difference between `IN` and `EXISTS` in MS SQL?
Ans: `IN` is used to check if a value matches any value in a list or subquery. `EXISTS` is used to check if a subquery returns any rows.
44. What is a Synonym in MS SQL?
Ans: A Synonym is an alternative name for another database object, such as a table or view, in the same or another database.
45. What is the purpose of the `MERGE` statement in MS SQL?
Ans: The `MERGE` statement performs `INSERT`, `UPDATE`, or `DELETE` operations on a target table based on the results of a join with a source table.
46. What is a Filtered Index in MS SQL?
Ans: A Filtered Index is an optimized nonclustered index that indexes only a subset of rows in a table, based on a filter expression.
47. What is the purpose of the `OUTPUT` clause in MS SQL?
Ans: The `OUTPUT` clause returns information from, or expressions based on, each row affected by an `INSERT`, `UPDATE`, `DELETE`, or `MERGE` statement.
48. What is a Partitioned Table in MS SQL?
Ans: A Partitioned Table is a table that is divided into smaller, more manageable pieces called partitions, which can improve performance and manageability.
Looking for more Microservice resources? Read our Master Microservices: Top 30 Interview Questions and Answers for 2025 to build a strong foundation!
49. What is the purpose of the `ROW_NUMBER()` function in MS SQL?
Ans: The `ROW_NUMBER()` function assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for the first row in each partition.
50. What is the difference between `RANK()` and `DENSE_RANK()` functions?
Ans: `RANK()` assigns a unique rank to each row within a partition, with gaps in ranking when ties occur. `DENSE_RANK()` assigns ranks without gaps, even when ties occur.
51. What is the purpose of the `PIVOT` and `UNPIVOT` operators in MS SQL?
Ans: `PIVOT` rotates data from rows to columns. `UNPIVOT` rotates columns into rows.
52. What is a Linked Server in MS SQL?
Ans: A Linked Server allows SQL Server to execute commands against OLE DB data sources outside of the SQL Server instance.
53. What is the purpose of the `TRY...CATCH` block in MS SQL?
Ans: The `TRY...CATCH` block is used to handle errors in a SQL Server session by capturing and responding to errors that occur within the `TRY` block.
54. What is a Full-Text Index in MS SQL?
Ans: A Full-Text Index allows for efficient querying of large text columns, such as `VARCHAR`, `NVARCHAR`, and `TEXT`, using natural language queries.
55. What is the purpose of the `FILESTREAM` attribute in MS SQL?
Ans: The `FILESTREAM` attribute allows storing large binary data directly on the file system while maintaining transactional consistency with the database.
56. What is a Computed Column in MS SQL?
Ans: A Computed Column is a virtual column that is not physically stored in the table but is computed based on other columns in the same table.
57. What is the purpose of the `APPLY` operator in MS SQL?
Ans: The `APPLY` operator allows a table-valued function to be invoked for each row returned by an outer table expression of a query.
58. What is the difference between `CROSS APPLY` and `OUTER APPLY`?
Ans: `CROSS APPLY` returns only rows from the outer table that produce a result set. `OUTER APPLY` returns all rows from the outer table, with `NULL` values for rows that do not produce a result set.
59. What is a Table Variable in MS SQL?
Ans: A Table Variable is a variable that stores a result set defined with a table structure. It is declared with the `DECLARE` statement and has a limited scope.
60. What is the purpose of the `OFFSET` and `FETCH` clauses in MS SQL?
Ans: The `OFFSET` clause specifies the number of rows to skip before starting to return rows. The `FETCH` clause specifies the number of rows to return after the `OFFSET`.
61. What is a Recursive Query in MS SQL?
Ans: A Recursive Query is a query that refers to itself, allowing it to iterate over a hierarchy of data, such as an organizational chart or bill of materials.
62. What is the purpose of the `LEAD()` and `LAG()` functions in MS SQL?
Ans: `LEAD()` provides access to a row at a given physical offset which follows the current row. `LAG()` provides access to a row at a given physical offset which comes before the current row.
63. What is a Sequence Object in MS SQL?
Ans: A Sequence Object generates a sequence of numeric values, which can be used for generating unique numbers, such as primary keys.
64. What is the purpose of the `FORMAT()` function in MS SQL?
Ans: The `FORMAT()` function converts a value to a specified format, such as a date or number format.
65. What is the difference between `DATETIME` and `DATETIME2` data types?
Ans: `DATETIME` has a larger storage size and less precision compared to `DATETIME2`, which offers a more precise range and smaller storage size.
66. What is the purpose of the `TRY_CONVERT()` function in MS SQL?
Ans: The `TRY_CONVERT()` function attempts to convert a value to a specified data type and returns `NULL` if the conversion fails, instead of raising an error.
67. What is the purpose of the `TRY_CAST()` function in MS SQL?
Ans: The `TRY_CAST()` function attempts to cast a value to a specified data type and returns `NULL` if the cast fails, instead of raising an error.
68. What is the purpose of the `STRING_AGG()` function in MS SQL?
Ans: The `STRING_AGG()` function concatenates values from multiple rows into a single string, with a specified separator.
69. What is the purpose of the `STRING_SPLIT()` function in MS SQL?
Ans: The `STRING_SPLIT()` function splits a string into rows of substrings, based on a specified separator.
70. What is the purpose of the `PARSE()` function in MS SQL?
Ans: The `PARSE()` function converts a string to a specified data type using a specified culture.
71. What is the purpose of the `CONCAT()` function in MS SQL?
Ans: The `CONCAT()` function concatenates two or more string values and returns the resulting string.
72. What is the purpose of the `CONCAT_WS()` function in MS SQL?
Ans: The `CONCAT_WS()` function concatenates strings with a specified separator, skipping any `NULL` values.
73. What is the purpose of the `TRANSLATE()` function in MS SQL?
Ans: The `TRANSLATE()` function replaces specified characters in a string with other specified characters.
74. What is the purpose of the `REPLACE()` function in MS SQL?
Ans: The `REPLACE()` function replaces all occurrences of a specified string value with another string value.
75. What is the purpose of the `STUFF()` function in MS SQL?
Ans: The `STUFF()` function deletes a specified length of characters and then inserts another set of characters at a specified starting point.
76. What is the purpose of the `REVERSE()` function in MS SQL?
Ans: The `REVERSE()` function returns a string in reverse order.
77. What is the purpose of the `LEN()` function in MS SQL?
Ans: The `LEN()` function returns the number of characters in a string.
78. What is the purpose of the `CHARINDEX()` function in MS SQL?
Ans: The `CHARINDEX()` function returns the starting position of a specified string within another string.
79. What is the purpose of the `PATINDEX()` function in MS SQL?
Ans: The `PATINDEX()` function returns the starting position of the first occurrence of a pattern in a specified expression.
80. What is the purpose of the `LEFT()` and `RIGHT()` functions in MS SQL?
Ans: The `LEFT()` function returns a specified number of characters from the left side of a string. The `RIGHT()` function returns a specified number of characters from the right side of a string.
81. What is the purpose of the `SUBSTRING()` function in MS SQL?
Ans: The `SUBSTRING()` function returns a part of a string, starting at a specified position and for a specified length.
82. What is the purpose of the `LTRIM()` and `RTRIM()` functions in MS SQL?
Ans: The `LTRIM()` function removes leading spaces from a string. The `RTRIM()` function removes trailing spaces from a string.
83. What is the purpose of the `UPPER()` and `LOWER()` functions in MS SQL?
Ans: The `UPPER()` function converts a string to uppercase. The `LOWER()` function converts a string to lowercase.
84. What is the purpose of the `SPACE()` function in MS SQL?
Ans: The `SPACE()` function returns a string of repeated spaces.
85. What is the purpose of the `REPLICATE()` function in MS SQL?
Ans: The `REPLICATE()` function repeats a string a specified number of times.
86. What is the purpose of the `QUOTENAME()` function in MS SQL?
Ans: The `QUOTENAME()` function returns a Unicode string with delimiters added to make the input string a valid SQL Server delimited identifier.
87. What is the purpose of the `UNICODE()` function in MS SQL?
Ans: The `UNICODE()` function returns the Unicode integer value of the first character of the input expression.
88. What is the purpose of the `NCHAR()` function in MS SQL?
Ans: The `NCHAR()` function returns the Unicode character with the specified integer code.
89. What is the purpose of the `ASCII()` function in MS SQL?
Ans: The `ASCII()` function returns the ASCII code value of the leftmost character of a character expression.
90. What is the purpose of the `CHAR()` function in MS SQL?
Ans: The `CHAR()` function returns the character with the specified ASCII code.
91. What is the purpose of the `BINARY_CHECKSUM()` function in MS SQL?
Ans: The `BINARY_CHECKSUM()` function returns the binary checksum value computed over a row of a table or a list of expressions.
92. What is the purpose of the `CHECKSUM()` function in MS SQL?
Ans: The `CHECKSUM()` function returns the checksum value computed over a row of a table or a list of expressions.
93. What is the purpose of the `NEWID()` function in MS SQL?
Ans: The `NEWID()` function generates a unique identifier of type `uniqueidentifier`.
94. What is the purpose of the `NEWSEQUENTIALID()` function in MS SQL?
Ans: The `NEWSEQUENTIALID()` function generates a unique identifier of type `uniqueidentifier` that is greater than any previously generated by the function.
95. What is the purpose of the `RAND()` function in MS SQL?
Ans: The `RAND()` function returns a random float value between 0 and 1.
96. What is the purpose of the `ABS()` function in MS SQL?
Ans: The `ABS()` function returns the absolute value of a number.
97. What is the purpose of the `CEILING()` function in MS SQL?
Ans: The `CEILING()` function returns the smallest integer greater than or equal to a specified value.
98. What is the purpose of the `FLOOR()` function in MS SQL?
Ans: The `FLOOR()` function returns the largest integer less than or equal to a specified value.
99. What is the purpose of the `ROUND()` function in MS SQL?
Ans: The `ROUND()` function returns a number rounded to a specified length or precision.
100. What is the purpose of the `POWER()` function in MS SQL?
Ans: The `POWER()` function returns the value of a specified expression to the power of another specified expression.
.png)
