Similar to the clustered index, a non-clustered index also contains the 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 non-clustered index, the physical order of the rows is not the same as the index order.
Non-clustered 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. The SQL Server creates non-clustered indexes by default when the CREATE INDEX command is given. There can be as many as 249 non-clustered indexes per table.
The data in a non-clustered index is present in a random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout a table. The non-clustered index tree contains the index keys in a sorted order, with the leaf level of the index containing a pointer to the data page and the row number in the data page.
The figure displays a non-clustered index present on the Eid attribute o the Employee table. To search for any value, the SQL Server would start from the root page and move down the B-Tree until it reaches a leaf page that contains a pointer to the required record. It would then use this pointer to access the record in the table. For example, to search for the record containing Eid E006 by using the non-clustered index, the following steps would be performed by the SQL Server:
Non-clustered 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. The SQL Server creates non-clustered indexes by default when the CREATE INDEX command is given. There can be as many as 249 non-clustered indexes per table.
The data in a non-clustered index is present in a random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout a table. The non-clustered index tree contains the index keys in a sorted order, with the leaf level of the index containing a pointer to the data page and the row number in the data page.
- The SQL Server perform the following steps when it uses a non-clustered index to search for a value:
- The 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 row-ID in the table and requested row is retrieved.
- The SQL Server starts from page 603, which is the root page.
- It searches for the highest key value on the page, the page with a key value less than or equal to the search value, that is, to the page containing the pointer to Eid E005.
- The search continues from page 602.
- Eid E005 is found and the search continues to page 203.
- Page 203 is searched to find a pointer to the actual row. Page 203 is the last page, or the leaf page, of the index.
- The search then moves to page 302 of the table to find the actual row.
In an index, more than one row can contain duplicate values. However, if you configure an index to contain unique values, the index will also contain unique values. Such an index is called a unique index. You can create a unique index on a columns that contain unique values, such as the primary key columns.A unique index can be clustered or non-clustered depending on the nature of the column