SQL Server Indexes - Definition

One of the most important technique to high performance queries in a SQL Server database is the index. Like we have index in books for fast searching, indexes speed up the querying process by providing fast access to rows in the data tables.

Indexes are of two types, clustered and nonclustered indexes.

Clustered Index

A clustered index is an index that stores the actual data. It is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A clustered index stores the actual data rows at the leaf level of the index. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table. A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Non-Clustered Index

A non-clustered index is just a pointer to the data. It is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and data row pointers , rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row pointer depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.