Type of Indexes in SQL Server - Clustered Indexes & Nonclustered Indexes

Type of Indexes
Clustered Indexes

1. A common analogy for a clustered index is a phone book. A phone book still sorts entries into alphabetical order. 
2. The difference is, once we find a name in a phone book, we have immediate access to the rest of the data for the name, such as the phone number and address.
3. For a clustered index, the database will sort the table's records according to the column (or columns) specified by the index. 
4. A clustered index contains all of the data for a table in the index, sorted by the index key, just like a phone book is sorted by name and contains all of the information for the person inline. 

create a clustered index:
CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID) 

• we can only have one clustered index per table,
•  In SQL Server, creating a primary key constraint will automatically create a clustered index (if none exists) using the primary key column as the index key.

Non-Clustered Indexes

1. A book index stores words in order with a reference to the page numbers where the word is located. 
2. This type of index for a database is a nonclustered index; only the index key and a reference are stored.
3. The nonclustered indexes created earlier in the chapter contain only the index key and a reference to find the data, which is more like a book index. 

A Disadvantage to Clustered Indexes

1. If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance.
2.  A table's clustered index can often be found on the primary key or a foreign key column, because key values generally do not change once a record is inserted into the database.