We know that Indexes have a single root node structurein SQL Server. SQL Server always uses this root node as the starting point for
traversing an index. SQL Server always ensures that the key columns are unique
for both types of index. Nonclustered indexes have the same B-Tree structure as
clustered indexes only with one significant difference. The leaf node of the nonclustered
index contains key values not the actual data. These key values map to pointer
or clustering keys that locate rows in the data pages.
How does Nonclustered
index implement?
The implementation of the nonclustered index depends
on whether the data pages of a table are managed as a Heap or as a clustered
index.
- Heap based Nonclustered Index structure in SQL Server - If nonclustered index is built in a heap then SQL Server uses pointers in the leaf node index pages that point a row in the data pages.
- Nonclustered Index based on Clustered Index Structure- If we have a table with clustered index, SQL Server builds a nonclustered index on the top of the clustered index structure and SQL Server uses clustering keys in the leaf node index page of the nonclustered index to point the cluster index.
How does
Nonclustered Index navigate on Clustered Index Structure?
In this section, we are studying on Nonclustered
Index Structure on the top of the Clustered Index structure. To understand its functionality,
we could take an alphabet example where all the data on the Nonclustered index
is built on the top of the clustered index structure as given below:
How does
SQL Server navigate a nonclustered index?
To understand this, we are taking the above
example and we need to find out the Alpha
No ‘1052’ from our data table where nonclustered index is built on the Alpha No data field and the same table
has a clustered index defined on the FirstName also. To pull the records from
this table, we are using below script as given below:
SELECT AlphaNo, FirstName, LastName
FROM dbo.DataTable
WHERE AlphaNo =1052
|
Nonclustered
Index Root node Navigation
SQL Server starts at the root node of the nonclustered
index and evaluated that 1052 is greater than or equal to the key value actor 1024
and the compression result is to true. Now, SQL server moves to next key value 1052
on the root node and do the same compression evaluation which is to true again.
Now, SQL server moves to next key value 1074 on the root node and do the same
compression evaluation which is false. In this case, SQL Server uses the
previous key value 1052 on the root node and moves to intermediate node.
Nonclustered
Index Intermediate node Navigation
After moving to intermediate node, SQL Server
evaluated that 1052 is greater than or equal to the key value actor 1045 where
the compression result is to true. Now,
SQL server moves to next key value 1052 on the intermediate node and
compression evaluation result is true. In this case, SQL Server moves to Leaf
node.
Nonclustered
Index Leaf node Navigation
SQL Server reads through the leaf node until it
finds 1052 and then uses this point value that allocated with the data page clustering key on
the clustered index.
Clustered
Index Data Page Navigation
SQL Server reads through the data page until it
finds the key value is equal to 1052 in clustered index. SQL Server returns this
row to the query processor. The pages in the data chain and the rows in them
are ordered on the value of the nonclustered index key.
Conclusion
SQL Server stores its indexes in B-Tree format except ColumnStore indexes where each node in such a tree is represented by a single page or node. With a
nonclustered index, there is a second list that has pointers to the physical
rows. Nonclustered indexes can be built on either a heap or a clustered index.
They always contain a row locator back to the base table.
How does SQL Server navigate a clustered index?
References: https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx
No comments:
Post a Comment