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%'
