CODEWITHSUNDEEP
sql-servernon-clustered-index
userx
userx

Reputation: 3773

Why is there no heap rid present in the unique non-clustered index root page but is present in root page of non-unique non clustered index?

I am doing some analysis and trying to gather some understanding of unique and non-unique non clustered indexes on SQL Server 2008 ? Following a technical blog,I did the following.

     USE TEST 
     CREATE TABLE "CustomersHeap" (
        "CustomerId" INT NOT NULL,
        "CustomerName" CHAR(100) NOT NULL,
        "CustomerAddress" CHAR(100) NOT NULL,
        "Comments" CHAR(189) NOT NULL
    )
    ;


    USE TEST 
    DECLARE @i INT = 1 WHILE (@i <= 80000) BEGIN
    INSERT INTO dbo.CustomersHeap VALUES
    (
    @i,
    'CustomerName' + CAST(@i AS CHAR),
    'CustomerAddress' + CAST(@i AS CHAR),
    'Comments' + CAST(@i AS CHAR)
    ) SET @i += 1 
    END

    -- Create a unique non clustered index 
    CREATE UNIQUE NONCLUSTERED INDEX IDX_UniqueNCI_CustomerID 
    ON Test.dbo.CustomersHeap(CustomerId) 


    -- Create a non-unique non clustered index 
    CREATE NONCLUSTERED INDEX IDX_NonUniqueNCI_CustomerID 
    ON Test.dbo.CustomersHeap(CustomerId) 

    --Get indexes
    select * from sys.indexes where object_id=OBJECT_ID('Test.dbo.CustomersHeap')


    --After figuring out index and child pages using DBCC IND
    DBCC TRACEON(3604)
    --UniqueNonClusteredIndex root page
    DBCC PAGE(Test, 1, 4160, 3)
    --NonUniqueNonClusteredIndex root page  
    DBCC PAGE(Test, 1, 4416, 3)

The output of first page statement does not contain a column called 'HEAP RID' where as the output of second page statement does. I am unable to understand the reason why ? especially when all the rows have information of ChildPageId and they should contain 'HEAP RID' rightfully.

This additional columns adds to the size of the root page of Non-Unique Non Clustered Index.

Output columns for first page statement FileId,PageId,Row,Level,ChildFileId,ChildPageId,CustomerId,KeyHashValue

Output columns for second page statement FileId,PageId,Row,Level,ChildFileId,ChildPageId,CustomerId,HEAP RID,KeyHashValue

Upvotes: 2

Views: 148

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

Please read these articles (both written by Klaus Aschenbrenner):

Excerpt:

"The conclusion here is that the unique non-clustered index on a non-unique clustered index makes only the 4 byte overhead in the leaf level of the clustered index, because here SQL Server directly points to the correct record. There is no additional overhead involved in the non-leaf levels of the unique non-clustered index."

Since it directly points to the correct record there is no heap RID.

Since the unique key is itself a unique identifier of row, there is no RID present in a unique CI or unique NCI. RID happens when there is no unique key present in that row, so SQL Server adds a row indentifier (RID) to locate the row and climb up the B-tree.

So the RID has nothing to do with CI or NCI. RID is related with uniqueness of the row.

Upvotes: 1

Related Questions