7 questions foundWhat is an index in SQL Server, and how does it help speed up queries?
Beginner An index creates a separate, organized structure that lets SQL Server quickly locate specific rows in a table without scanning every single row, similar to how the index at the back of a book lets you jump directly to a topic instead of reading every page to find it.
CREATE INDEX IX_Customers_LastName
ON Customers(LastName);
Real-world example An online store creates an index on the customer's last name column, dramatically speeding up a customer service search feature that frequently looks up customers by their last name.
Common follow-ups: Does adding more indexes always improve query performance?;What is the tradeoff of having too many indexes on a table?
Query Optimization & Plans;Data Types & Schema Design
What is the difference between a clustered index and a nonclustered index?
Beginner A clustered index determines the actual physical order in which data is stored on disk, meaning a table can only have one clustered index, while a nonclustered index creates a separate structure that points back to the actual data, allowing a table to have many nonclustered indexes in addition to its single clustered index.
-- Clustered index, often on the primary key
CREATE CLUSTERED INDEX IX_Orders_OrderId ON Orders(OrderId);
-- Nonclustered index for a different search pattern
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId ON Orders(CustomerId);
Real-world example An orders table uses a clustered index on OrderId since that matches how data is typically inserted and retrieved, while a separate nonclustered index on CustomerId speeds up a common query that looks up all orders for a specific customer.
Common follow-ups: Why can a table only have one clustered index?;Does creating a primary key automatically create a clustered index?
Query Optimization & Plans;Constraints (Primary Key Foreign Key Check & Unique)
What is a covering index, and how does it help avoid an expensive lookup operation?
Intermediate A covering index includes all the columns a specific query needs, either as key columns or as included columns, meaning SQL Server can satisfy the entire query directly from the index itself without needing to go back to the actual table to retrieve additional column values, which is a common source of performance improvement.
CREATE NONCLUSTERED INDEX IX_Orders_Covering
ON Orders(CustomerId)
INCLUDE (OrderDate, Amount);
Real-world example A reporting query that filters by CustomerId and selects OrderDate and Amount runs significantly faster after a covering index is created that includes those exact columns, avoiding the need to look up additional data from the main table.
Common follow-ups: What is the difference between key columns and included columns in an index?;How do you identify which queries would benefit from a covering index?
Query Optimization & Plans;Columnstore Indexes
How do you identify unused or duplicate indexes that might be safely removed to improve write performance?
Intermediate You can query dynamic management views like sys.dm_db_index_usage_stats to see how often each index is actually being used for reads versus how much overhead it adds to writes, and compare index definitions across a table to spot duplicate or overlapping indexes that provide little additional benefit while still slowing down every insert and update.
SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName,
s.user_seeks, s.user_scans, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id;
Real-world example A database administrator finds several indexes on a heavily written table that have never actually been used for a read in months, and safely removes them to speed up the table's frequent insert operations.
Common follow-ups: What is the risk of removing an index that appears unused?;How often should index usage statistics be reviewed?
Query Optimization & Plans;SQL Server Profiler & Extended Events
How does index fragmentation happen over time, and how do you address it?
Advanced As data is inserted, updated, and deleted, an index's logical order can become increasingly out of sync with its physical storage order, called fragmentation, which can slow down queries that rely on scanning the index, and is typically addressed by periodically reorganizing lightly fragmented indexes or fully rebuilding heavily fragmented ones.
-- Reorganize for lighter fragmentation
ALTER INDEX IX_Orders_CustomerId ON Orders REORGANIZE;
-- Rebuild for heavier fragmentation
ALTER INDEX IX_Orders_CustomerId ON Orders REBUILD;
Real-world example A database maintenance job checks index fragmentation levels every weekend, reorganizing indexes with moderate fragmentation and fully rebuilding the most heavily fragmented ones to keep query performance consistent.
Common follow-ups: What fragmentation percentage typically triggers a reorganize versus a full rebuild?;Does rebuilding an index require the table to be offline?
Query Optimization & Plans;SQL Server Agent & Job Scheduling
How do filtered indexes work, and what specific scenarios benefit most from using one?
Advanced A filtered index only includes rows that match a specific WHERE condition defined when the index is created, making it smaller and more efficient than indexing the entire table, which is especially useful for tables where queries commonly filter on a small, well defined subset of data, such as only active or incomplete records.
CREATE NONCLUSTERED INDEX IX_Orders_Pending
ON Orders(OrderDate)
WHERE Status = 'Pending';
Real-world example An order processing system creates a filtered index that only covers pending orders, since queries checking for pending orders run frequently, and the filtered index stays much smaller and faster than indexing every order regardless of status.
Common follow-ups: How much smaller is a typical filtered index compared to indexing the whole table?;Can a filtered index be used for queries that do not include the same filter condition?
Query Optimization & Plans;Columnstore Indexes
How do you decide which columns to choose, and in what order, when creating a composite index covering multiple columns?
Intermediate You generally place the column most commonly used in equality comparisons, such as an exact match in a WHERE clause, first in the index, followed by columns used for range comparisons or sorting, since SQL Server can use a composite index most effectively when queries filter on a matching left to right prefix of its columns.
-- Good choice if queries commonly filter by CustomerId first, then sort by OrderDate
CREATE INDEX IX_Orders_Customer_Date
ON Orders(CustomerId, OrderDate);
Real-world example A reporting query that always filters by a specific customer and then sorts by order date benefits significantly from a composite index with CustomerId listed first and OrderDate listed second, matching the query's actual filtering and sorting pattern.
Common follow-ups: What happens if a query only filters on the second column of a composite index?;How many columns is it reasonable to include in a single composite index?
Query Optimization & Plans;Data Types & Schema Design