SQL Server Interview Questions - Intermediate Level

Create and Use an Index

Create a non-clustered index on a frequently queried column in a sample table and measure query performance before and after.

Expected output: Improved query performance with fewer logical reads.

Hint: Use SQL Server Management Studio to create the index and SET STATISTICS IO ON to measure reads.

Write a Transaction Script

Write a SQL Server transaction that transfers funds between two accounts ensuring atomicity.

Expected output: Funds are transferred only if both debit and credit succeed.

Hint: Use BEGIN TRANSACTION, COMMIT, and ROLLBACK with error handling.

A clustered index sorts and stores the data rows physically in the table, allowing only one per table. A non-clustered index is a separate structure that points to the data rows and multiple non-clustered indexes can exist per table.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable transaction processing: atomicity guarantees all or nothing execution; consistency ensures data integrity; isolation manages concurrent transactions; durability guarantees committed transactions persist.

Stored procedures are precompiled, reducing parsing and compilation time. They also reduce network traffic by executing multiple statements in one call and improve security by controlling access to underlying data.