SQL Server : Part 5 : Explaining Non clustered Index on Heap

In the last post, we have discussed about non clustered index on a clustered table.In this post we will discuss about the non clustered index on a heap table. 

Non clustered index can be created on clustered table as well as heap table.While creating a non clustered index on clustered table , clustered index key will act as a row pointer. In heap table ,the combination of file id , page number and slot number will act as a row pointer in the non  clustered index.

Let us see a hands on example. We will create a copy of salesorderdetail table with a non clustered index on productid and salesorderid columns

SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
CREATE UNIQUE INDEX Ix_ProductId ON SalesOrderDetailHeap(ProductId,Salesorderid)

A pictorial representation of the b tree structure is given below

Let us run the DBCC IND command to find the root page of the index.

DBCC ind('mydb','SalesOrderDetailHeap',2)



This statement returns 289 records with 1 as maximum value in the indexlevel column. That means the index structure required one page in root node , 287 pages in leaf level and one page for IAM chain.The page number of record, having maximum value for indexlevel column (root page) is 1656. Let us see the root page.

DBCC traceon(3604)
GO
DBCC PAGE ('mydb',1,1656,3)

Below we can see a part of the output of DBCC page command. The structure of the output is same as the structure of root page of non clustered index on clustered table.

Fig 1

Now let us move to the page 1497 which is a leaf level page.

DBCC traceon(3604)
GO
DBCC PAGE ('mydb',1,1497,3)

Fig 2

 In the leaf level of non clustered index on clustered table , we have noticed that , clustered key is added to leaf level pages along with nonclustered key.Here we do not have a clustered index. So SQL server added row identifier (8 bytes in size) which is a combination of fileid (2 bytes) ,pageid(4 bytes) and slot number(2 bytes). The row identifier can be found in the  HeapRID column of Fig 2. From the above figure, it is clear that, the complete information of the record with productid=707 and salesorderid =49464 can be found at the location HeapRID 0x6813000001000600. Below query will help us to split the RID into FileId:PageId:SlotNo format

DECLARE @HeapRid BINARY(8)

SET @HeapRid = 0x6813000001000600SELECT
    
       CONVERT (VARCHAR(5),
                    CONVERT(INT, SUBSTRING(@HeapRid, 6, 1)
                               + SUBSTRING(@HeapRid, 5, 1)))
     + ':'
     + CONVERT(VARCHAR(10),
                    CONVERT(INT, SUBSTRING(@HeapRid, 4, 1)
                               + SUBSTRING(@HeapRid, 3, 1)
                               + SUBSTRING(@HeapRid, 2, 1)
                               + SUBSTRING(@HeapRid, 1, 1)))
     + ':'
          + CONVERT(VARCHAR(5),
                    CONVERT(INT, SUBSTRING(@HeapRid, 8, 1)
                               + SUBSTRING(@HeapRid, 7, 1)))
                               AS 'Fileid:Pageid:slot'

The output of the query is 1:4968:6 which says File id =1, Pageid =4968 and slot no=6. Let us see the page number 4968

DBCC traceon(3604)

GO

DBCC PAGE ('mydb',1,4968,3)

In the output, you can see all columns  of record having productid=707 and  salesordeid = 49464 at slot number 6. 

Let us consider  the below query and analyse how SQL sever fetch the data.

SET STATISTICS IO ON

GO
SELECT *  FROM SalesOrderDetail WHERE productid=707 AND SalesOrderid=49464

SQL Server  has to do two I/O operation to reach the leaf level of non clustered index and one I/O operation to fetch the remaining data using Heap RID from the heap structure.The execution plan of the query is given below

Even if we make changes in the query to fetch only the ProductId,SalesOrderid and SalesorderDetaildId columns, still SQL sever need to do Key lookup operation. This is because SalesOrderDetailid is not declared as clustered key and not available in the leaf level of the nonclustered index. To avoid the key lookup operation, we need to limit the select columns only to the non clustered key(ProductKey ,salesorderid).

If you liked this post, do like my page on FaceBook