MSSQL Objective Questions and Answers for Practitioners Set-1
1. Which isolation level allows the highest level of concurrency and provides the least locking on the database?
Explanation: The READ UNCOMMITTED isolation level allows the highest level of concurrency, as it allows transactions to read uncommitted changes made by other transactions. However, this level of isolation also leads to "dirty reads," where a transaction can read data that has been modified but not yet committed by another transaction. It provides the least amount of locking, but it can result in inconsistent data being read.
2. Which type of index is designed to improve the performance of queries that return a small result set from large tables?
Explanation: A Filtered Index is designed to improve the performance of queries that return a small result set from large tables by including only a subset of rows that meet a specific condition. This reduces the index size and maintenance overhead while still providing efficient query performance for the relevant subset of data.
3. What is the purpose of the SQL Server function "RAISERROR"?
Explanation: The "RAISERROR" function in SQL Server is used to display custom error messages and raise user-defined errors. It allows you to control the error severity, state, and custom error message, providing better error handling and communication to users or applications.
4. Which statement is used to add a new column to an existing table in MSSQL?
Explanation: The "ADD COLUMN" statement is used to add a new column to an existing table in MSSQL. It allows you to specify the column name, data type, and any constraints or defaults associated with the new column.
5. Which SQL statement is used to delete rows from a table based on a specified condition?
Explanation: The "DELETE" statement is used to remove rows from a table based on a specified condition. It is commonly used for data maintenance and cleanup tasks.
6. What is the purpose of the SQL Server function "COALESCE"?
Explanation: The "COALESCE" function in SQL Server is used to return the first non-null expression from a list of expressions. If the first expression is null, it evaluates the next expression until it finds a non-null value.
7. What is the purpose of the SQL Server function "DATEDIFF"?
Explanation: The "DATEDIFF" function in SQL Server is used to calculate the difference between two dates, expressed in the specified date part (such as days, months, years, etc.).
8. Which SQL data type would you use to store large amounts of character data that may exceed 8,000 characters in MSSQL?
Explanation: The "NVARCHAR(MAX)" data type in MSSQL is used to store large amounts of Unicode character data. It can hold up to 2 GB of data, making it suitable for storing extensive text.
9. What does the SQL Server function "CHECKSUM" do?
Explanation: The "CHECKSUM" function in SQL Server calculates and returns a hash value for a group of rows in a table. It is useful for detecting changes or ensuring data integrity in the group of rows.
10. Which SQL statement is used to permanently remove a table from the database in MSSQL?
Explanation: The "DROP TABLE" statement is used to permanently remove a table and its data from the database in MSSQL. This action cannot be undone, so use it with caution.
11. What is the purpose of the SQL Server function "STUFF"?
Explanation: The "STUFF" function in SQL Server is used to delete a specified length of characters from a string and replace them with another string.
12. Which SQL statement is used to modify the structure of an existing table in MSSQL?
Explanation: The "ALTER TABLE" statement is used to modify the structure of an existing table in MSSQL. It allows you to add, modify, or drop columns, as well as add or drop constraints and indexes.
13. What is the purpose of the SQL Server function "STRING_SPLIT"?
Explanation: The "STRING_SPLIT" function in SQL Server is used to split a string into multiple rows based on a specified delimiter. Each part of the string separated by the delimiter becomes a separate row in the result set.
14. What is the purpose of the SQL Server function "ROW_NUMBER()" when used with the OVER() clause?
Explanation: The "ROW_NUMBER()" function with the OVER() clause in SQL Server is used to assign a unique sequential number to each row in a result set. It is often used for pagination or ranking purposes
15. What is the purpose of the SQL Server function "PIVOT"?
Explanation: The "PIVOT" function in SQL Server is used to rotate or convert rows into columns based on the values of a specified column. It is particularly useful when you want to transform data from a vertical format to a horizontal format.
Rate Your Experience
: 89
: 1
Last updated in November, 2024
Quick Access