| Previous | Home | Next |
The data is physically stored there is only one clustered index can be created per table
Clustered index must be build on attribute that have a high percentage of unique values and they are not modified often A clustered indexes should be created before a nonclustered index
A clustered index changes the order of the rows.
How Clustered Indexes Work
In clustered index data are stored at the leaf level of the B-tree. The data pages of a table are like folders stored in an alphabetically order in the filling cabinet, and the rows of the data are like the documents stored in folders.
SQL server performs the following steps when it uses a clustered index to search for a value:
Step 1. First SQL sever find the root page from the sysindexes table
Step 2. The search value is compared with the key values on the root page
Step 3. The page with the highest key value less than or equal to the search value is found
Step 4. The page pointer is followed the next lower level in the index
Step 5.Step 3 and 4 repeated until the data page is reached
Step 6.The rows of the 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
Example:
Consider the following example in which the rows of the employee table are sorted according to the Eid attribute and stored in the table.
If the row containing Eid E006 were to be searched using the clustered index displayed in the figure, the following step will be followed.
SQL Server starts from page 603, the root page It searches the highest key value on the page, which is less than or equal to the search value. The result of this search is 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 and 203 is searched to find the required row.
| Previous | Home | Next |