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 WHERE clauses, and whose values may be modified frequently.
SQL server creates nonclustered indexes by default when the CREATE INDEX command is given
There can be as many as 249 nonclustered indexes per table Typically, nonclustered indexes are created on foreign keys.A nonclustered index would need to be rebuilt if it is built before a clustered index
How Nonclustered Indexes Work
- Step First SQL sever find the rosot page from the sysindexes table.
- Step The search value is compared with the key values on the root page.
- Step The page with the highest key value less than or equal to the search value is found.
- Step The page pointer is followed the next lower level in the index.
- StepStep 3 and 4 repeated until the data page is reached.
- Step The rows are searched on the leaf page for the specified value. if a matched is not found, the table contains no matching rows.
- Step If a match is found, the pointer is followed to the data page and row-ID in the table, and the requested row is retrieved.
Example:If the row containing Eid E006 were to be searched using the nonclustered index displayed in the figure, the following steps would be followed:
SQL Server starts from page 603, the root page It searches 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 There, the Eid E005 is found and the search continues on page 203. Page 203 is searched to find a pointer to the actual row.
Page 203 is the last, or the leaf page of the indexThe search then moves to page 302 of the table to find the actual row.