Clustered Index & Nonclustered Index

Clustered Index 

A clustered index is an index that sorts and stores the data rows in the table based on their key values. Therefore, the data is physically sorted in the table when a clustered index is defined on it. Only one clustered index can be created per table. Therefore, you should build the clustered index on attributes that have a high percentage of unique values and are not modified often. In a clustered index, data is stored at the leaf level of the B- Tree.
 SQL Server performs the following steps when it uses a clustered index to search for a value:
SQL Server obtains the address of the root page from the sysindexes table, which is a system table containing the details of all the indexes in the database. The search value is compared with the key values on the root page. The page with the highest key value less than or equal to the search value is found. The page pointer is followed to the next lower level in the index. Steps 3 and 4 are repeated until the data page is reached. The rows of data are searched on the data page until the search value is found. If the search value is not found on the data page, no rows are returned by the query.

Nonclustered Index 

Similar to the clustered index, a nonclustered index also contains the index key values and the row locators that point to the storage location of the data in a table. However, in a nonclustered index, the physical order of the rows is not the same as the index order. Nonclustered indexes are typically created on columns used in joins and the WHERE clause. These indexes can also be created on columns where the values are modified frequently. SQL Server creates nonclustered indexes by default when the CREATE INDEX command is given. There can be as many as 999 nonclustered indexes per table. The data in a nonclustered index is present in a random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout the
table. The nonclustered index tree contains the index keys in a sorted order, with the leaf level of the index containing a pointer to the data page.
 SQL Server performs the following steps when it uses a nonclustered index to search for a value:

SQL Server obtains the address of the root page from the sysindexes table. The search value is compared with the key values on the root page. The page with the highest key value less than or equal to the search value is found. The page pointer is followed to the next lower level in the index. Steps 3 and 4 are repeated until the data page is reached. The rows are searched on the leaf page for the specified value. If a match is not found, the table contains no matching rows. If a match is found, the pointer is followed to the data page and the requested row is retrieved.