MSDN Documentation

Creating Indexes in SQL Server

Indexes are critical for improving query performance in SQL Server. Understanding how to create and manage them is a fundamental skill for database administrators and developers.

What is an Index?

An index is a database structure that improves the speed of data retrieval operations on a table. It works similarly to an index in a book; it contains keys, and for each key, it has a pointer to the row in the table that contains the corresponding key.

Types of Indexes

SQL Server supports several types of indexes, with the most common being:

• Clustered Indexes: Define the physical order of data in the table. A table can have only one clustered index.
• Nonclustered Indexes: Do not affect the physical order of data. They are separate structures that contain index keys and pointers to data rows. A table can have multiple nonclustered indexes.
• Unique Indexes: Enforce that index key values are unique.
• Filtered Indexes: Indexes on a subset of rows in a table, defined by a WHERE clause.

Creating a Basic Nonclustered Index

The simplest way to create an index is by using the CREATE INDEX statement.

CREATE INDEX IX_CustomerLastName
ON Customers (LastName);
                

This statement creates a nonclustered index named IX_CustomerLastName on the LastName column of the Customers table.

Creating a Clustered Index

A clustered index is typically created on the primary key of a table. If a table has a primary key constraint, SQL Server often automatically creates a clustered index on it. You can also create it explicitly.

-- Assuming CustomerID is the primary key
CREATE CLUSTERED INDEX PK_Customers
ON Customers (CustomerID);
                

Note: If a table already has a clustered index, you cannot create another one.

Creating a Unique Index

A unique index ensures that no duplicate values exist in the indexed column(s).

CREATE UNIQUE INDEX UQ_ProductCode
ON Products (ProductCode);
                

Creating a Composite Index

A composite index includes multiple columns. The order of columns in a composite index is important for query optimization.

CREATE INDEX IX_OrderDateAndCustomerID
ON Orders (OrderDate, CustomerID);
                

Creating a Filtered Index

Filtered indexes can significantly improve performance and reduce index maintenance overhead by indexing only a subset of data.

CREATE NONCLUSTERED INDEX IX_OpenOrders
ON Orders (CustomerID)
WHERE OrderStatus = 'Open';
                

Index Options

The CREATE INDEX statement supports various options, such as:

• IGNORE_DUP_KEY: Prevents duplicate key errors for unique indexes.
• DROP_EXISTING: Drops an existing index before creating a new one.
• FILLFACTOR: Controls how full each leaf page of the index is.
• PAD_INDEX: Applies the FILLFACTOR to intermediate-level index pages.

Best Practices for Indexing

• Index frequently queried columns: Columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses are good candidates.
• Avoid over-indexing: Too many indexes can slow down data modification operations (INSERT, UPDATE, DELETE).
• Choose appropriate index types: Use clustered indexes for columns that are frequently searched in a range. Use nonclustered indexes for other queries.
• Consider composite indexes: For queries that filter or join on multiple columns.
• Monitor index usage: Regularly review index fragmentation and unused indexes.
• Use filtered indexes: For tables with a large number of rows where queries often target specific subsets.

Managing Indexes

You can use the following Transact-SQL statements to manage indexes:

Effective index management is crucial for maintaining optimal database performance. Refer to the official SQL Server documentation for advanced techniques and specific scenarios.