Creating and Using Indexes in SQL Server

Indexes are data structures that speed up data retrieval in SQL Server. Without indexes, every query would need to scan the entire table (a table scan). With the right indexes, SQL Server can locate rows in milliseconds instead of seconds. However, indexes come with trade-offs: they consume disk space and slow down write operations.

Clustered Indexes

A clustered index determines the physical order of data in the table. Each table can have only one clustered index. When you create a primary key, SQL Server automatically creates a clustered index on that column unless you specify otherwise.

-- Primary key creates a clustered index by default
CREATE TABLE Customers (
    CustomerID INT IDENTITY PRIMARY KEY,  -- clustered index
    CustomerName NVARCHAR(100),
    Email VARCHAR(200)
);

-- Explicitly create a clustered index
CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON Orders (OrderDate);

Non-Clustered Indexes

Non-clustered indexes are separate structures that contain the indexed columns and a pointer back to the table data. A table can have up to 999 non-clustered indexes, though you should be selective.

-- Index on a frequently queried column
CREATE NONCLUSTERED INDEX IX_Employees_Department
ON Employees (Department);

-- Composite index for multi-column queries
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON Orders (CustomerID, OrderDate DESC);

Covering Indexes with INCLUDE

A covering index contains all the columns a query needs, eliminating the need to look up the base table. Use the INCLUDE clause to add non-key columns to the index leaf level.

-- This index covers the query below completely
CREATE NONCLUSTERED INDEX IX_Employees_Dept_Covering
ON Employees (Department)
INCLUDE (FirstName, LastName, Salary);

-- This query is fully satisfied by the index
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Department = 'Engineering';

Unique Indexes

A unique index enforces that no duplicate values exist in the indexed columns. It also serves as a constraint.

CREATE UNIQUE NONCLUSTERED INDEX IX_Users_Email
ON Users (Email);

Filtered Indexes

Filtered indexes include only rows that match a WHERE condition. They are smaller, faster, and cheaper to maintain than full indexes.

-- Only index active employees
CREATE NONCLUSTERED INDEX IX_Employees_Active
ON Employees (Department, LastName)
WHERE IsActive = 1;

Checking Index Usage

SQL Server tracks index usage statistics. Use the DMVs to identify unused or underperforming indexes.

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
INNER JOIN sys.indexes i
    ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY s.user_seeks DESC;

Proper indexing is the single most impactful optimization you can make in SQL Server. Start with indexes on columns used in WHERE, JOIN, and ORDER BY clauses, then use execution plans to identify missing indexes and refine your strategy.