SQL Server : Part 7 : Non clustered index on non unique column

In our earlier  post, we have discussed about the non clustered index, but there we were always discussing  about unique non clustered index to make the discussion simple.As we understood the general structure of the non clustered index, let us discuss the storage structure of a non clustered index on a non unique column.

In our last post, we have discussed how SQL server manage clustered key on a non unique column.In that post we learned that SQL server add 4 bytes value to all duplicate occurrence of the clustered key.In the same way, non clustered index add the  cluster key in all level of the b tree to uniquely identify the records in the next level.In the case of clustered index, the uniquifier is added only to the duplicate occurrence.In the case of non clustered index , clustered key is added to all records if the uniqueness is not defined while creating the index. If the non clustered index is defined as unique, SQL server adds the clustered key only to the leaf level for the bookmark look up operation .

Let us see a sample . 

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

A copy  of Salesorderdetail table is created with unique clustered index on SalesOrderDetailId and a non clustered index on ProductId and SalesOrderId column.Note that, while  creating the non clustered index , I have purposefully avoided the unique keywords even if the non clustered index key is unique.

DBCC ind('mydb','SalesOrderDetail',4)

DBCC IND returns 229 records and root page id is 8320. Let us see the output of DBCC page for the root page.

DBCC page ('mydb',1,8320,3)

In the below figure, I have combined the output of the root page and one of the leaf level page. If you look into the root page (the first part), you can see that the cluster key (SalesOrderDetailid) is added in the root page.If you go back to our earlier discussion on non clustered index on clustered table , you will not find the clustered key in the root level. it will be there only in the leaf level. There is no change in the leaf level page structure while defining non clustered index as  unique or non unique.

Let us see what will happen if the  table is on heap.

SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
CREATE INDEX Ix_ProductId ON SalesOrderDetailHeap (ProductId,Salesorderid)
GO
SELECT index_id FROM sys.indexes WHERE name='Ix_ProductId' AND 
OBJECT_NAME(OBJECT_ID)='SalesOrderDetailHeap'
GO
DBCC ind('mydb','SalesOrderDetailHeap',2)
GO
DBCC page ('mydb',1,5352,3)

In the below figure, I have combined the output of the root page and one leaf level page.In the root page you can notice that Heap RID is added. If you go back our earlier discussion on non clustered index on heap, we can see the Heap RID only on the leaf level not in the root page. There is no change in the leaf level page structure while defining non clustered index as unique or non unique.






























You might have noticed that ,in our above example, even if the non clustered key is unique , SQL server considered it as non unique as we did not mentioned the uniqueness while creating the non clustered index. Adding the clustered key (or HEAP RID) in to all level of index might lead to increase the level of index (more IO) depends on the size of the clustered key.So it is important to consider the uniqueness of column while defining the non clustered index key in all possible situation.

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