SQL Server interview questions covering T-SQL, indexes, joins, stored procedures, transactions, execution plans, security, and performance.
SQL Server is Microsoft relational database platform. It supports T-SQL, transactions, indexes, stored procedures, security, replication, high availability, analytics features, backup and restore, and performance tooling. It is commonly used for enterprise applications that need strong consistency, reporting, and operational support.
T-SQL, or Transact-SQL, is SQL Server procedural extension to SQL. It adds variables, error handling, stored procedures, functions, transactions, temporary objects, control-of-flow statements, and SQL Server-specific system functions.
A clustered index defines the physical or logical order of rows in a table storage structure. A table can have only one clustered index. It is often placed on a stable, narrow, unique, ever-increasing key, but the best choice depends on query patterns and write behavior.
CREATE CLUSTERED INDEX CX_Orders_OrderId
ON dbo.Orders(OrderId);A nonclustered index is a separate structure that stores indexed key columns plus a row locator back to the base table. It speeds reads for matching predicates and joins, but adds storage and write overhead.
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);Included columns are non-key columns stored at the leaf level of a nonclustered index. They help create covering indexes without making every returned column part of the sort key. This can reduce key lookups for read-heavy queries.
CREATE INDEX IX_Orders_Customer_Date
ON dbo.Orders(CustomerId, OrderDate)
INCLUDE (TotalAmount, Status);A covering index contains all columns needed by a query, either as key columns or included columns. SQL Server can satisfy the query from the index without looking up rows in the clustered index or heap. Covering indexes are powerful but should be balanced against write cost.
An execution plan shows how SQL Server intends to execute a query. It includes operators such as scans, seeks, joins, sorts, lookups, and aggregates. Actual execution plans include runtime details that help compare estimates with reality.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM dbo.Orders WHERE CustomerId = 42;An index seek navigates directly to a targeted range in an index. An index scan reads many or all index pages. Seeks are often faster for selective filters, but scans can be appropriate when many rows are needed or the table is small.
Parameter sniffing happens when SQL Server compiles a plan using the first parameter values it sees, then reuses that plan for later values. This can be good when values are typical, but bad when data is skewed. Fixes include better indexes, updated stats, OPTION(RECOMPILE), OPTIMIZE FOR, or query design changes.
CREATE OR ALTER PROCEDURE dbo.GetOrders
@CustomerId int
AS
BEGIN
SELECT * FROM dbo.Orders WHERE CustomerId = @CustomerId;
END;Statistics describe data distribution and help the optimizer estimate row counts. Bad or stale statistics can produce poor plans. SQL Server can auto-create and auto-update statistics, but high-change tables may need targeted maintenance.
UPDATE STATISTICS dbo.Orders IX_Orders_CustomerId;A stored procedure is a saved T-SQL program in the database. It can encapsulate queries, writes, validation, transactions, and permissions. Stored procedures reduce repeated SQL text and can improve security, but business logic placement should be intentional.
CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
@CustomerId int
AS
BEGIN
SET NOCOUNT ON;
SELECT OrderId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId;
END;SET NOCOUNT ON prevents SQL Server from sending row count messages after each statement. It is commonly used in stored procedures to reduce network chatter and avoid confusing client code that expects result sets.
Functions return scalar values or tables. Inline table-valued functions can perform well because they are expanded into the calling query. Scalar functions and multi-statement table-valued functions can cause performance problems depending on SQL Server version and usage.
A view is a saved query exposed like a table. Views simplify access, hide complexity, and can provide security boundaries. Regular views do not store data; they run the underlying query when referenced.
CREATE VIEW dbo.ActiveCustomers AS
SELECT CustomerId, Email
FROM dbo.Customers
WHERE IsActive = 1;An indexed view materializes view results with a unique clustered index. It can improve repeated aggregate or join-heavy queries, but adds write overhead and has strict requirements. Use it only when measured read benefits justify maintenance cost.
A transaction groups operations so they commit or roll back together. Transactions protect consistency during multi-step changes such as order creation, payment recording, or inventory updates. Keep transactions short to reduce lock duration.
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT;Isolation levels control how transactions see and block each other. SQL Server supports Read Uncommitted, Read Committed, Repeatable Read, Serializable, Snapshot, and Read Committed Snapshot. Higher isolation reduces anomalies but can increase blocking or version store usage.
Locking protects data consistency while transactions read or modify data. SQL Server can use row, page, table, key-range, shared, update, exclusive, and intent locks. Locking problems often show up as blocking, timeouts, or deadlocks.
A deadlock occurs when sessions wait on each other in a cycle. SQL Server chooses a victim and rolls it back. Reduce deadlocks by accessing objects in a consistent order, keeping transactions short, indexing queries well, and retrying safely in application code.
tempdb is a system database used for temporary tables, table variables, sorts, hash operations, row versioning, online index operations, and internal workspace. tempdb contention or disk pressure can affect the whole SQL Server instance.
Temporary tables, such as #Items, have statistics and are often better for larger intermediate result sets. Table variables are scoped variables and can be convenient for small sets. Modern SQL Server improved table variable behavior, but temp tables are still often better for complex workloads.
CREATE TABLE #RecentOrders (OrderId int PRIMARY KEY);
DECLARE @Ids TABLE (OrderId int PRIMARY KEY);A Common Table Expression is a named temporary query expression created with WITH. CTEs improve readability and support recursive queries. They are not automatically materialized; SQL Server expands them into the query plan.
WITH RecentOrders AS (
SELECT * FROM dbo.Orders WHERE OrderDate >= DATEADD(day, -7, SYSDATETIME())
)
SELECT CustomerId, COUNT(*)
FROM RecentOrders
GROUP BY CustomerId;Window functions calculate values across related rows without collapsing rows like GROUP BY. They are useful for rankings, running totals, previous-row comparisons, and top-N-per-group problems.
SELECT
CustomerId,
OrderId,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
FROM dbo.Orders;MERGE can insert, update, or delete target rows based on matching source rows. It is concise for sync logic, but has edge cases and concurrency concerns. Many teams prefer separate INSERT/UPDATE statements unless MERGE behavior is well tested.
MERGE dbo.Target AS t
USING dbo.Source AS s
ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Name = s.Name
WHEN NOT MATCHED THEN INSERT (Id, Name) VALUES (s.Id, s.Name);Identity columns auto-generate values for a table column. Sequences are independent objects that generate numbers and can be shared across tables. Both can have gaps, so do not use them when gap-free business numbering is required.
CREATE SEQUENCE dbo.OrderNumber AS bigint START WITH 1000 INCREMENT BY 1;
SELECT NEXT VALUE FOR dbo.OrderNumber;Triggers run automatically after or instead of INSERT, UPDATE, or DELETE operations. They can enforce auditing or special rules, but they can hide side effects and slow writes. Triggers must handle multi-row operations correctly.
SQL Server Agent jobs automate scheduled tasks such as backups, ETL, index maintenance, reports, alerts, and cleanup. Production jobs need owners, retry behavior, notifications, logging, and monitoring.
SQL Server supports full, differential, and transaction log backups. Full backups capture the database, differential backups capture changes since the last full backup, and log backups capture transaction log records for point-in-time recovery in full or bulk-logged recovery models.
Recovery models control transaction log behavior and restore options. Simple recovery is easier to manage but does not support point-in-time restore. Full recovery supports log backups and point-in-time recovery. Bulk-logged can reduce logging for some bulk operations but has restore tradeoffs.
ALTER DATABASE AppDb SET RECOVERY FULL;
BACKUP LOG AppDb TO DISK = 'AppDb_log.trn';A restore strategy defines how backups are used to recover to the required point. It should include RPO, RTO, full/differential/log backup order, test restores, corruption checks, and who performs recovery during incidents.
RESTORE DATABASE AppDb FROM DISK = 'AppDb_full.bak' WITH NORECOVERY;
RESTORE LOG AppDb FROM DISK = 'AppDb_log.trn' WITH RECOVERY;Always On Availability Groups provide high availability and disaster recovery by replicating databases to secondary replicas. They can support automatic failover, readable secondaries, and listener-based routing, but require careful design for quorum, latency, jobs, logins, and backups.
Replication copies data between SQL Server instances using models such as transactional, snapshot, or merge replication. It is useful for reporting, integration, or distributed systems, but it adds operational complexity and is not a universal HA replacement.
Partitioned tables split data across partitions based on a partition function and scheme. They help manage large tables, archival, sliding windows, and some query patterns. Partitioning is not a substitute for good indexing and can add complexity.
Columnstore indexes store data by column rather than by row. They are excellent for analytics, scans, aggregations, and data warehouse workloads. Rowstore indexes are often better for point lookups and OLTP writes.
Query Store captures query texts, plans, runtime statistics, and plan history. It helps identify regressions, compare plans, and force a known good plan when appropriate. It is one of the most useful SQL Server performance troubleshooting tools.
Dynamic SQL builds a SQL statement as text and executes it. It is useful for flexible search, dynamic object names, or optional filters, but it can introduce SQL injection and plan-cache issues. Use sp_executesql with parameters whenever possible.
DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.Users WHERE Email = @Email';
EXEC sp_executesql @sql, N'@Email nvarchar(320)', @Email = N'a@example.com';TRY...CATCH handles errors in T-SQL batches, stored procedures, and transactions. Use XACT_STATE() to decide whether a transaction can commit or must roll back after an error.
BEGIN TRY
BEGIN TRAN;
-- work here
COMMIT;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK;
THROW;
END CATCH;A schema is a namespace and security boundary inside a database. Examples include dbo, sales, audit, and security. Schemas help organize objects and simplify permissions management.
SQL Server uses logins at the server level and users at the database level. Roles group permissions, and GRANT, DENY, and REVOKE control access. Applications should use least privilege rather than db_owner or sysadmin.
CREATE USER app_user FOR LOGIN app_login;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::sales TO app_user;Row-level security filters rows based on a security predicate function. It is useful for multi-tenant systems and data access policies. It must be tested carefully because incorrect predicates can leak or hide data.
Transparent Data Encryption, or TDE, encrypts database files, backups, and log files at rest. It protects storage media, but it does not replace column-level encryption, TLS, access control, or application security.
DELETE removes rows and can filter with WHERE; it logs row deletes. TRUNCATE removes all rows by deallocating pages and resets identity values, but has restrictions. DROP removes the table object itself.
DELETE FROM dbo.Logs WHERE CreatedAt < '2025-01-01';
TRUNCATE TABLE dbo.TempImport;
DROP TABLE dbo.OldTable;Use dynamic management views such as sys.dm_exec_requests and sys.dm_exec_sessions to identify blockers, wait types, and SQL text. Blocking should be tied back to transaction length, missing indexes, isolation levels, and application behavior.
SELECT session_id, blocking_session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;Wait stats show where SQL Server sessions spend time waiting, such as CPU, I/O, locks, memory grants, or network. They are a high-level diagnostic signal, not a final answer. Always connect waits to queries, workload, and hardware.
Common mistakes include missing indexes, too many indexes, stale statistics, non-sargable predicates, SELECT *, implicit conversions, large transactions, scalar UDF misuse, unbounded result sets, parameter sniffing surprises, and ignoring execution plans.
A sargable predicate can use an index efficiently. Wrapping indexed columns in functions or causing implicit conversions can make predicates non-sargable and force scans.
-- Less sargable
WHERE YEAR(OrderDate) = 2026
-- More sargable
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'Check the query plan and workload first, create the index during a safe window or use online options if available, monitor blocking and log growth, and validate the query actually improves. Indexes speed reads but slow writes and use storage.
CREATE INDEX IX_Orders_Status_Date
ON dbo.Orders(Status, OrderDate)
INCLUDE (CustomerId, TotalAmount);Common mistakes include using sysadmin for applications, SQL injection through concatenated dynamic SQL, weak passwords, exposed ports, missing encryption, overbroad schema permissions, unprotected backups, and storing secrets in procedures or jobs.
Common anti-patterns include using cursors for set-based work without reason, NOLOCK everywhere, triggers with hidden side effects, giant stored procedures, no backup restore tests, shrinking databases routinely, and tuning by guesswork instead of plans and metrics.
A strong design uses primary keys, foreign keys, constraints, appropriate data types, useful indexes, and realistic access patterns. Explain how the schema supports both correctness and common queries.
CREATE TABLE dbo.Customers (
CustomerId int IDENTITY(1,1) PRIMARY KEY,
Email nvarchar(320) NOT NULL UNIQUE,
CreatedAt datetime2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE TABLE dbo.Orders (
OrderId bigint IDENTITY(1,1) PRIMARY KEY,
CustomerId int NOT NULL REFERENCES dbo.Customers(CustomerId),
TotalAmount decimal(12,2) NOT NULL CHECK (TotalAmount >= 0),
OrderDate datetime2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Orders_Customer_Date
ON dbo.Orders(CustomerId, OrderDate DESC);Explore 500+ free tutorials across 20+ languages and frameworks.