SQL Server Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations on a table. An index speeds up data retrieval by creating a pointer to rows in a table based on the values in one or more columns. When a query uses an indexed column, the database can use the index to find the rows instead of scanning the entire table.

Types of Indexes

Clustered Indexes

A clustered index defines the physical order of data in the table. Because data pages can only be stored in one physical order, a table can have only one clustered index. The leaf nodes of a clustered index contain the actual data rows. Creating a clustered index on a column such as a primary key (which is usually unique) is common and often beneficial for performance.

Nonclustered Indexes

A nonclustered index is a separate structure from the data rows. It contains the index key values and a row locator for each key value. The row locator is either a pointer to the clustered index key if the table has a clustered index, or a pointer to the data page if the table does not have a clustered index.

A table can have multiple nonclustered indexes. These are useful for columns frequently used in the WHERE clause or JOIN conditions.

Unique Indexes

A unique index enforces the uniqueness of the indexed column(s). This means that no two rows in the table can have the same index key value. This is often used for primary keys, but can also be applied to other columns where duplicate values are not permitted.

Columnstore Indexes

Columnstore indexes are optimized for data warehousing workloads. They store data column by column, rather than row by row. This allows for high compression ratios and faster analytical queries that aggregate large amounts of data.

Creating Indexes

Indexes are created using the CREATE INDEX statement. The syntax varies slightly depending on the type of index.

Example: Creating a Clustered Index

CREATE CLUSTERED INDEX IX_Customers_CustomerID
ON dbo.Customers (CustomerID);

Example: Creating a Nonclustered Index

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate);

Example: Creating a Unique Nonclustered Index

CREATE UNIQUE NONCLUSTERED INDEX UQ_Products_ProductCode
ON dbo.Products (ProductCode);

Managing Indexes

Regular maintenance is crucial for index performance. This includes rebuilding or reorganizing indexes to combat fragmentation.

Fragmentation

Fragmentation occurs when the logical order of data in an index does not match the physical order of the rows in the data pages. This can slow down queries.

• Internal Fragmentation: Occurs within index pages.
• External Fragmentation: Occurs when pages are not contiguous.

Reorganizing and Rebuilding

• ALTER INDEX ... REORGANIZE: Defragments the leaf level of the index and consolidates pages. It's an online operation.
• ALTER INDEX ... REBUILD: Rebuilds the entire index, creating a fresh copy. This is generally more effective at removing fragmentation but can be more resource-intensive and can cause more locking if not performed online.

-- Reorganize an index
ALTER INDEX IX_Orders_OrderDate ON dbo.Orders REORGANIZE;

-- Rebuild an index (online if Enterprise Edition)
ALTER INDEX IX_Customers_CustomerID ON dbo.Customers REBUILD;