Create Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional write operations, which are used to maintain the data structure. Indexes can also be used to enforce uniqueness.

Types of Indexes

SQL Server supports several types of indexes, including:

• Clustered Indexes: Determine 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 contain key values and pointers to the data rows.
• Unique Indexes: Ensure that all values in the index key are unique.
• Filtered Indexes: Optimize query performance by creating an index on a subset of rows in a table.
• Columnstore Indexes: Provide high compression and query performance for data warehousing workloads.
• XML Indexes: Optimized for querying XML data.
• Spatial Indexes: Optimized for querying spatial data.
• Full-Text Indexes: Used for full-text searching on character-based data.

Creating an Index

You can create an index using the CREATE INDEX Transact-SQL statement.

Syntax

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON schema_name.table_name ( column1 [ ASC | DESC ] [ , column2 [ ASC | DESC ] ... ] )
[ INCLUDE ( column3 [ , column4 ] ... ) ]
[ WITH ( option1 = value1 [ , option2 = value2 ] ... ) ]
[ ; ]

Example: Creating a Nonclustered Index

-- Create a nonclustered index on the 'LastName' column of the 'Customers' table
CREATE NONCLUSTERED INDEX IX_Customers_LastName
ON dbo.Customers (LastName ASC);

Example: Creating a Clustered Index

-- Create a clustered index on the 'CustomerID' column of the 'Orders' table
CREATE CLUSTERED INDEX PK_Orders_CustomerID
ON dbo.Orders (CustomerID);

Example: Creating a Unique Index

-- Create a unique index on the 'Email' column to ensure unique email addresses
CREATE UNIQUE NONCLUSTERED INDEX UQ_Users_Email
ON dbo.Users (Email);

Example: Creating a Filtered Index

-- Create a filtered index for active customers
CREATE NONCLUSTERED INDEX IX_Customers_Active
ON dbo.Customers (CustomerID)
WHERE IsActive = 1;

Index Options

The WITH clause allows you to specify various options that affect how the index is created and maintained, such as:

• FILLFACTOR: Specifies the percentage of leaf-level pages to be filled with data.
• PAD_INDEX: Specifies that allocated pages for index data are filled up to a specified percentage.
• SORT_IN_TEMPDB: Specifies whether to use the tempdb database to store intermediate sort results.
• STATISTICS_NORECOMPUTE: Specifies that statistics are not recomputed after the index is created.
• DROP_EXISTING: Specifies that if the index already exists, it will be dropped and recreated.

Index Maintenance

Regular maintenance of indexes is important to ensure optimal performance. This includes:

• Reorganizing Indexes: To address page splits and reduce fragmentation.
• Rebuilding Indexes: To completely rebuild the index and reclaim unused space, which also updates statistics.
• Updating Statistics: To ensure the query optimizer has accurate information about data distribution.

You can manage indexes using SQL Server Management Studio (SSMS) or Transact-SQL commands like ALTER INDEX.

Example: Rebuilding an Index

ALTER INDEX IX_Customers_LastName ON dbo.Customers REBUILD;

Example: Reorganizing an Index

ALTER INDEX IX_Customers_LastName ON dbo.Customers REORGANIZE;

When to Use Indexes

Indexes are most beneficial for:

• Columns that are frequently used in WHERE clauses.
• Columns that are used in JOIN conditions.
• Columns that are used in ORDER BY and GROUP BY clauses.
• Columns that are candidates for enforcing uniqueness.

Avoid creating indexes on columns with very low cardinality (few distinct values) or on tables with high write activity unless absolutely necessary.