In SQL Server, an index is a data structure that allows you to quickly retrieve data from a table based on the values of one or more columns. There are two main types of indexes in SQL Server: clustered and nonclustered indexes.

A clustered index

• Is a type of index that physically sorts and stores the data rows of a table in the order of the indexed columns. This means that the data rows are stored on the disk in the same order as the indexed columns, and the indexed columns are used as the key to look up and retrieve the data rows. A table can have only one clustered index, because the data rows can be physically sorted and stored in only one order.

A nonclustered index

• is a type of index that does not physically sort and store the data rows of a table. Instead, it stores the indexed columns and a pointer to the data rows in a separate structure, called an index tree. This allows the indexed columns to be used as a key to look up and retrieve the data rows, without changing the physical order of the data rows on the disk. A table can have multiple nonclustered indexes, because the data rows are not physically sorted and stored in the order of the indexed columns.

The main difference between a clustered and a nonclustered index is how the data rows are stored and accessed on the disk. A clustered index physically sorts and stores the data rows in the order of the indexed columns, while a nonclustered index stores the indexed columns and a pointer to the data rows in a separate structure. This affects the performance and space usage of the indexes, as well as the number and types of columns that can be indexed.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!