Microsoft SQL Server Interview Questions - 2025

1. What id Denormalization?

It is the process of improving the performance of the database by adding redundant data.

2. What is Normalization?

It is the process of eliminating redundant data and maintaining data dependencies.

3. ACID Properties?

Atomicity: It ensures all-or-none rule for database modifications.

Consistency: Data values are consistent across the database.

Isolation: Two transactions are said to be independent of one another.

Durability: Data is not lost even at the time of server failure.

4. Different types of Triggers?

They are three different types of triggers:

1. DML Triggers: These are of two kinds:

a. Instead of Triggers: These are invoked in place of the triggering action such as insert, update or delete.

b. After Triggers: These are invoked after the triggering action such as insert, update or delete.

2. DDL Triggers: These are invoked against DDL statements. These are always After Triggers.

3. Logon Triggers: These are invoked when a Logon event occurs and before the user session is established.

5. What is a linked server?

Linked server facilitates the ease of linking with heterogenous servers. Using linked servers, you can manipulate data on the remote servers and even integrate with local data.

Stored procedures: sp_linkedservers gives you the list of linked servers available on the server.

6. What is a cursor?

Cursor is a database object used to manipulate data in row-by-row basis.

Steps involved: Declare a cursor -> Open a cursor -> Fetch row from the cursor -> Process the row fetched -> Close the cursor -> Deallocate the cursor.

Don’t stop at Mirosoft SQL Server — explore Data Analytics, Python, and more at our Courses Page.

7. Difference between user defined functions and stored procedures?

User defined functions can be used anywhere in the queries i.e. within where/having/select section where as stored procedures cannot be used.

Note: Stored procedures can be used with insert statements. UDFs can also be used in join operations and UDFs can be used to return tables which can be joined with other tables.

8. How does truncate and delete operation effect Identity?

Truncate resets the Identity to its base value whereas delete does not reset it to the base value.

9. How does primary key constraint and unique key constraint effect null?

Primary key constraints allow no null values in the specified column whereas unique key constraint allows a single null value not multiple null value.

10. What is DEFAULT?

Default allows to add values to the column if the value of that column is not set. Default can be defined on number and datetime fields they cannot be defined on timestamp and IDENTITY columns.

11. What is Optimistic Locking and Pessimistic locking?

In optimistic locking, once you read a record, you verify the version number associated with the record is not changed when you write the record back. If the version number associated with the record is changed, then the transaction needs to be restarted and new values needs to be inserted.

Pessimistic locking is locking the record exclusively. They are of 4 isolation levels associated: readuncommitted, readcommitted, repeatable read and serializable. Serializable is the highest isolation level and its more advanced compared to optimistic locking.

12.Difference between exclusive lock and update lock?

In case of exclusive lock, no other lock can be acquired on that row or table. Every process has to wait until the process which holds the lock releases it.

In case of update lock, while reading the row or a record, you can have any other lock associated with that row or record. In case of updating the record, update lock changes itself to exclusive lock and no other process can obtain a lock on that row until the lock is released.

13. What is collation?

Collation defines a set of rules that determines how data is sorted and compared. Once the collation has been defined, you cannot change the collation rules until you re-create it or drop the entity.

14. How to check collation associated with a database?

SELECT collation_name from sys.databases where name = 'DATABASENAME'

15. What is DMV's and DMF's?

Data management views and data management functions provide information about the state of SQL Server in other words they are responsible for providing information about health of the SQL Server.

16. What are statistics?

Statistics define how well a query can be executed with low resource consumption.

17. What is difference between UNION and UNION ALL?

UNION is used for fetching distinct records across tables. UNION ALL displays all the records including duplicates. The processing time for UNION is more when compared to UNION ALL.

18. What is blocking?

SQL Server blocking occurs when one connection holds a lock on a record and other connection tries to fetch the record or update the record.

19. How deadlock is resolved?

Deadlock is automatically resolved by SQL Server. SQL Server identifies the process which has less overhead and accordingly it rollbacks the transaction associated with that process.

20. What are row constructors?

Row constructors allow you to insert multiple rows of data with a single insert statement.

21. What is OUTPUT clause?

OUTPUT clause is used for displaying the records effected by the use of the DML statements.

22. Requirements of a sub query?

Sub query has following requirements:

1. A sub query needs to be enclosed in parenthesis.
2. A subquery cannot have order by clause.
3. A query can have more than one sub-query.
4. A sub query needs to be on right hand side of the comparison operator.

23. What is a filegroup?

Filegroup is a collection of datafiles which are managed as a single unit. You can have one primary filegroup per database and many users defined filegroups. Log files cannot be part of a filegroup due to difference in structure.

24. Is table size reduced, when you delete data from the table?

No, the table size is not reduced, indeed the SQL Server marks those rows as free rows. Once you insert the new data, the free rows will get updated and then the size of the table is changed based on the data insertion. If the data is not inserted, then after a while, the rows are eliminated.

25. Difference between GETDATE and SYSDATETIME()?

GETDATE uses the precision in milliseconds and SYSDATETIME in 100 nanoseconds.

Need personalized guidance? Connect with us today via the Contact Us Page and start your IT career journey at TrainingHub.io!