2

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

userx
  • 3,713
  • 5
  • 32
  • 38
  • My understanding of this is very incomplete, but my guess is that perhaps the Heap RID is added to the non-unique index to make it (or rather, the keys in it) internally unique? – stakx - no longer contributing Apr 23 '17 at 07:34
  • 1
    That is, in the case of the unique index, SQL Server knows that the `CustomerId` by itself will suffice in order to locate a specific table row with the full data. In case of the non-unique index, there might be several rows with the same `CustomerId` in the original table, so SQL Server makes the "foreign key" in the index unique by adding the internal (and unique) RIDs, ie. by turning the index key into a composite one. – stakx - no longer contributing Apr 23 '17 at 07:58

1 Answers1

1

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.

stakx - no longer contributing
  • 83,039
  • 20
  • 168
  • 268
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • @KumarHarsh- Both the blogs are written by the same guy. The question here is just related to the root level index page and not the leaf level page.Its the diagnostics of root page i have question on. – userx Apr 26 '17 at 06:36
  • @100pipers, check my last para. – KumarHarsh Apr 26 '17 at 08:29
  • @kumarharsh- So are we saying that since the non unique non clustered index can have 100 keys with same id say 1, in order to differentiate all these 1s, sql server attaches an rid ? Thanks. – userx Apr 26 '17 at 14:37
  • @100pipers , Absolutely.http://www.sqlservercentral.com/blogs/practicalsqldba/2013/03/24/sql-server-part-7-non-clustered-index-on-non-unique-column-/ Then also read part 4 – KumarHarsh Apr 27 '17 at 02:59