Sql-server – the purpose of this Uniq1002 column in this index scan

database-internalsexecution-plansql serversql-server-2012

Take the following repro:

USE tempdb;

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t
GO
CREATE TABLE dbo.t
(
    id int NOT NULL 
        PRIMARY KEY 
        NONCLUSTERED 
        IDENTITY(1,1)
    , col1 datetime NOT NULL
    , col2 varchar(800) NOT NULL
    , col3 tinyint NULL
    , col4 sysname NULL
);

INSERT INTO dbo.t (
      col1
    , col2
    , col3
    , col4
    ) 
SELECT TOP(100000) 
      CONVERT(datetime, 
         DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2000-01-01 00:00:00'))
    , replicate('A', 800)
    , sc2.bitpos
    , CONVERT(sysname, CHAR(65 + CRYPT_GEN_RANDOM(1) % 26) 
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26) 
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26))
FROM sys.syscolumns sc
    CROSS JOIN sys.syscolumns sc2;

Here I'm adding a clustered index onto a set of columns that are not unique, and typical single-column non-clustered index:

CREATE CLUSTERED INDEX t_cx 
ON dbo.t (col1, col2, col3);

CREATE INDEX t_c1 ON dbo.t(col4);

This query forces SQL Server to do a lookup into the clustered index. Please forgive the use of the index hint, it was the quickest way to get the repro:

SELECT id
    , col1
    , col2
    , col3
FROM dbo.t aad WITH (INDEX = t_c1)
WHERE col4 = N'JSB'
    AND col1 > N'2019-05-30 00:00:00';

The actual query plan shows a non-existent column in the Output List for the nonclustered index scan:

enter image description here

Ostensibly, this represents the uniqifier used in the non-unique clustered index. Is that the case? Is a column named like that always the clustered index uniqifier?

Best Answer

Ostensibly, this represents the uniqifier used in the non-unique clustered index. Is that the case?

Yes.

What is the purpose of this Uniq1002 column in this index scan?

Each row in the nonclustered index must be associated with exactly one row in the base table so that Bookmark Lookups (RID or Key) work correctly. This mapping is provided by the "row locator".

For heap tables, the row locator is the RID. For clustered row store tables, it is the clustering key(including the uniquifier where necessary).

For the Key Lookup in your plan to work, it must have access to the row locator. This includes the uniquifier, so it must be emitted by the nonclustered index scan.

The uniquifier is stored in the variable-length portion of the row so it only takes up space when needed (i.e. when a duplicate key actually exists).

Is a column named like that always the clustered index uniquifier?

Yes. The uniquifier column is always named UniqXXXX. The row locator associated with heap tables is named BmkXXXX. The row locator for a columnstore table is named ColStoreLocXXXX.

Observing the uniquifier

It is possible to directly observe the values of the uniquifier on SQL Server versions that contain a functional query_trace_column_values Extended Event.

This undocumented and unsupported event is in the Debug channel. It was introduced with SQL Server 2016, and stopped working around CU11 of SQL Server 2017.

For example:

CREATE TABLE #T (c1 integer NULL INDEX ic1 CLUSTERED, c2 integer NULL INDEX ic2 UNIQUE, c3 integer NULL);
GO
INSERT #T
    (c1, c2, c3)
VALUES 
    (100, 101, 0),
    (100, 102, 1),
    (100, 103, 2);
GO
DBCC TRACEON (2486);
SET STATISTICS XML ON;
SELECT T.* FROM #T AS T WITH (INDEX(ic2));
SET STATISTICS XML OFF;
DBCC TRACEOFF (2486);
GO
DROP TABLE #T;

Has the plan:

Plan

It produces event output like the following on SQL Server 2016:

Event output

Related Question