What is the purpose of this Uniq1002 column in this index scan?
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:



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