T-SQL script to test Uniquifier size

SET NOCOUNT ON;

-- DROP TABLE #NonUnique;
CREATE TABLE #NonUnique (Col1 INT NOT NULL, Col2 NVARCHAR(10) NULL);
INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'A'); -- 0x21
INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'B'); -- 0x21
INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'C'); -- 0x21
INSERT INTO #NonUnique (Col1, Col2) VALUES (34, N'D'); -- 0x22
GO
CREATE CLUSTERED INDEX #NU ON #NonUnique (Col1);
GO
CREATE NONCLUSTERED INDEX #NUnc ON #NonUnique (Col2) WITH (FILLFACTOR = 100);
GO

DECLARE @DatabaseID INT = DB_ID(N'tempdb'),
        @ObjectID INT = OBJECT_ID(N'tempdb..#NonUnique'),
        @FileID INT,
        @ClusteredIndexPageID INT,
        @NonClusteredIndexPageID INT;

SELECT  @FileID = dpa.allocated_page_file_id,
        @ClusteredIndexPageID = CASE WHEN dpa.index_id = 1 THEN dpa.allocated_page_page_id ELSE @ClusteredIndexPageID END,
        @NonClusteredIndexPageID = CASE WHEN dpa.index_id = 2 THEN dpa.allocated_page_page_id ELSE @NonClusteredIndexPageID END
FROM sys.dm_db_database_page_allocations(@DatabaseID, @ObjectID, NULL, NULL, 'DETAILED') dpa
WHERE is_iam_page = 0
--AND   [page_type] = 1; -- DATA_PAGE

SELECT @DatabaseID AS [DatabaseID],
       @ObjectID AS [ObjectID],
       @FileID AS [FileID],
       @ClusteredIndexPageID AS [ClusteredIndexPageID],
       @NonClusteredIndexPageID AS [NonClusteredIndexPageID];

/* CHECK THE CLUSTERED INDEX */
DBCC PAGE (@DatabaseID, @FileID, @ClusteredIndexPageID, 3) WITH TABLERESULTS;
----- Row #1: Col1 = 33 (unique); Uniquifier = 0 bytes; Total bytes for row = 19
-- Slot 0 Offset 0x60 Length 19 Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER  0
-- Slot 0 Offset 0x60 Length 19 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1    33

----- Row #2: Col1 = 33 (1st duplicate); Uniquifier = 4 bytes; Total bytes for row = 23
-- Slot 1 Offset 0x73 Length 23 Slot 1 Column 0 Offset 0x11 Length 4 Length (physical) 4    UNIQUIFIER  1
-- Slot 1 Offset 0x73 Length 23 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1    33

----- Row #3: Col1 = 33 (2nd duplicate); Uniquifier = 4 bytes; Total bytes for row = 23
-- Slot 2 Offset 0x8a Length 23 Slot 2 Column 0 Offset 0x11 Length 4 Length (physical) 4    UNIQUIFIER  2
-- Slot 2 Offset 0x8a Length 23 Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1    33

----- Row #4: Col1 = 34 (unique); Uniquifier = 0 bytes; Total bytes for row = 19
-- Slot 3 Offset 0xa1 Length 19 Slot 3 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER  0
-- Slot 3 Offset 0xa1 Length 19 Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1    34


/* CHECK THE NON-CLUSTERED INDEX */
DBCC PAGE (@DatabaseID, @FileID, @NonClusteredIndexPageID, 3) WITH TABLERESULTS;
-- Result Set #2 (easier to see this particular info) is shown below.
-- "KeyHashValue" column has been removed.
-- Notice rows 0 and 3, the "unique" rows for Col1, are 14 bytes while the other two rows are 20 bytes each.

/*
FileId    PageId    Row  Level     Col2 (key)     Col1 (key)     UNIQUIFIER (key)    Row Size
1         312       0    0         A              33             0                   14
1         312       1    0         B              33             1                   20
1         312       2    0         C              33             2                   20
1         312       3    0         D              34             0                   14
*/