Paul White (imported from SE)
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?
Top Answer
AMtwo (imported from SE)
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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.