What does non clustered index points to when the table has clustered index that is not unique?

Question

Assume table has name and id columns.

ID is unique non clustered index. So this points to the heap table.

If I set the Name as clustered index (non-unique), then what will the ID non clustered index point to?

I know that it won't point to the heap because the table is now a clustered index.

Usually a non clustered index points to the clustered key but in above scenario that clustered key is not unique. So what does the ID (non clustered index) point to?

Answer 1

The non-clustered index (NC) points to the clustered index (CL) key, even if the CL index isn't unique.

SQL Server adds a uniqueifier when a duplicate is encountered in the clustering key. What I think is often overlooked is that this uniqueifier is only added for the duplicates.

If you run out of uniqueifiers (exceed about 4 billion duplicates for the clustering key) you get the dreaded error 666, and you should probably rethink your choice of the clustering key:

The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

Here's an example showing that you don't pay the 4 bytes penalty for the first instance of a value:

--Table to play with
DROP TABLE IF EXISTS t
CREATE TABLE t(c1 char(3), c2 char(3))
CREATE CLUSTERED INDEX x ON t(c1)
GO

--Table to hold output from DBCC PAGE
DROP TABLE IF EXISTS dbccpage
CREATE TABLE dbccpage(ParentObject_ varchar(200), Object_ varchar(200), Field_ varchar(200), Value_ varchar(1000))
GO

INSERT INTO t VALUES('aaa', 'AAA'), ('aaa', 'A2A'), ('aaa', 'A3A'), ('bbb', 'BBB')

--This doesn't show enough details regarding uniqueifier
SELECT *
FROM t 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS f
CROSS APPLY sys.dm_db_page_info(DB_ID(), f.file_id, f.page_id, 'Detailed')

--DBCC PAGE to the rescue

--Get output to calling app
DBCC TRACEON(3604)
GO

DECLARE 
 @dbid int = Db_ID()
,@fileno int
,@pageno int
,@sql varchar(1000)

SELECT TOP(1) @fileno = f.file_id, @pageno = page_id
FROM t 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS f

SET @sql = CONCAT('DBCC PAGE(', @dbid, ', ', @fileno, ', ', CAST(@pageno AS varchar(10)), ', 1) WITH TABLERESULTS' )
PRINT @sql

INSERT INTO dbccpage
EXEC(@sql)

--Output:
SELECT Field_, Value_
FROM dbccpage
WHERE ParentObject_ LIKE 'DATA:' OR ParentObject_ like 'Slot%'

Answer 2

When you have non-clustered indexes built over a heap, the leaf-level nodes of the index store the Row ID (RID) pointers that are used to locate the data rows in the heap. The RID points to a physical location. It consists of the file identifier (ID), page number, and number of the row on the page.

In a unique clustered index, the leaf-level nodes store the clustered index key values to locate the data rows in much the same way it does with the heap, with the unique key values taking the place of the RID.

For a non-unique clustered index, the leaf-level nodes store the clustered index key values plus a uniqueifier that is generated and managed by the Database Engine to locate individual data rows. The uniqueifier is 4 bytes in length.

The combination of the clustered index keys and the uniqueifier is unique and therefore allows a data row to be located when doing a key lookup for example.

This is covered in some older documentation but is still valid now.