SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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
CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetail ON dbo.SalesOrderDetail(SalesOrderDetailID)
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
CREATE INDEX Ix_ProductId ON SalesOrderDetailHeap (ProductId,Salesorderid)
SELECT index_id FROM sys.indexes WHERE name='Ix_ProductId' AND 
DBCC ind('mydb','SalesOrderDetailHeap',2)
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


I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.


Leave a comment on the original post [www.practicalsqldba.com, opens in a new window]

Loading comments...