In SQL Server, a non-unique nonclustered index on a *rowstore* table incorporates the base object's *bookmark* (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is stored as part of the nonclustered index *key* at all index levels.
On the other hand, if the nonclustered index is *unique*, the bookmark is present only at the *leaf* level of the index - not as part of the key (the bookmark is present as one or more included columns, in effect).
In SQL Server 2016, it is possible to build a nonclustered b-tree index on a column-oriented table (one that has a clustered columnstore index).
1. What is the 'bookmark' used for a nonclustered b-tree index on a clustered columnstore table?
2. Do the differences between unique and non-unique nonclustered indexes described above still apply?
1. The "bookmark" is the *columnstore index original locator* (per "Pro SQL Server Internals" by Dmitri Korotkevitch). This is an 8-byte value, with the columnstore index's `row_group_id` in the first 4-bytes and an offset in the second 4-bytes.
2. If you use `DBCC PAGE` to look at the non-clustered index, the 8-byte columnstore index original locator appears in the "uniquifier" column of the `DBCC PAGE` output. This shows that a *unique* non-clustered index does not need to include the columnstore row locator, whereas a *non-unique* non-clustered index does.
The following code creates a columnstore-organized table with a unique and non-unique b-tree nonclustered index on the same column:
CREATE TABLE dbo.Heapish
(
c1 bigint NOT NULL,
c2 bigint NOT NULL,
INDEX CCI_dbo_Heapish CLUSTERED COLUMNSTORE
);
GO
INSERT dbo.Heapish WITH (TABLOCKX)
(c1, c2)
SELECT TOP (1024 * 1024 * 8)
c1 = ROW_NUMBER() OVER
(ORDER BY C1.[object_id], C1.column_id),
c2 = ROW_NUMBER() OVER
(ORDER BY C1.[object_id], C1.column_id)
FROM master.sys.columns AS C1
CROSS JOIN master.sys.columns AS C2
ORDER BY
c1
OPTION (MAXDOP 1);
GO
CREATE UNIQUE NONCLUSTERED INDEX UNIQUE_c2 ON dbo.Heapish (c2) WITH (MAXDOP = 1);
CREATE NONCLUSTERED INDEX NONUNIQUE_c2 ON dbo.Heapish (c2) WITH (MAXDOP = 1);
We can see the size of the index row at different levels of the b-tree using [`sys.dm_db_index_physical_stats`][1]:
SELECT
DDIPS.index_level,
DDIPS.page_count,
DDIPS.record_count,
DDIPS.min_record_size_in_bytes,
DDIPS.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID(N'dbo.Heapish', N'U'),
INDEXPROPERTY(OBJECT_ID(N'dbo.Heapish', N'U'), N'UNIQUE_c2', 'IndexID'),
NULL, 'DETAILED'
) AS DDIPS;
SELECT
DDIPS.index_level,
DDIPS.page_count,
DDIPS.record_count,
DDIPS.min_record_size_in_bytes,
DDIPS.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID(N'dbo.Heapish', N'U'),
INDEXPROPERTY(OBJECT_ID(N'dbo.Heapish', N'U'), N'NONUNIQUE_c2', 'IndexID'),
NULL, 'DETAILED'
) AS DDIPS;
The output is:
[![Unique index][2]][2]
[![Nonunqiue index][3]][3]
Both structures have the same row size at the leaf level, but the nonunique nonclustered index is 12 bytes larger than the unique nonclustered index at the non-leaf levels due to the 8-byte columnstore locator, plus 4 bytes of overhead for the first variable-length column in a row (uniquifier is variable length).
[1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql
[2]: https://i.stack.imgur.com/BpDTa.png
[3]: https://i.stack.imgur.com/zHcTB.png