RSS Feed

SQL Server - Composite index

SQL Server - Composite index

A composite index can be a clustered or non-clustered index. A composite index is composed of multiple key columns. Composite indexes in SQL Server (2005, 2008 & 2012) can include up to 16 columns that are all from the same table or view.

Example of Composite index:
Create CLUSTERED INDEX CIMyTable
ON Table_1 (Last_Name, First_Name)
The above example creates a composite clustered index on table named Table_1.

Few important points on composite indexes

1. The order of columns you give while creating a composite index is important. That is a composite index on (column_1, column_2) is not same as an index on (column_2, column_1).

2. While creating a composite index always define the most unique column first like in the above example we have taken last name column as the first column before first name.

3. We should try to create composite indexes for tables with multiple column keys.

When to create composite indexes

Try creating composite indexes when two or more columns are best searched as a key and queries mostly reference the columns in the index.



Learn more on indexes from:
Transact-SQL Programming with SQL Server 2005 and 2008
By Rigoberto Garcia PhD