Key count in sys[.]indexes

The old sysindexes table (as of 2005 implemented as a compatibility view) has a useful column named keycnt. This is supposed to give us the number of columns (keys) in the index. However, to make heads and tails out of the numbers, we need to understand how a non-clustered index is constructed. For a heap, the pointer to a row is the physical file/page/row address (aka “rowid”). This is counted as a key in the keycnt column:

IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
GO
CREATE TABLE T1 (c1 INT, c2 datetime, c3 VARCHAR(3))
CREATE INDEX ix_T1_c1 ON T1 (c1)
CREATE INDEX ix_T1_c1_c2 ON T1 (c1, c2)
CREATE INDEX ix_T1_c1_c2_c3 ON T1 (c1, c2, c3)
CREATE INDEX ix_T1_c2 ON T1 (c2)
SELECT name, keycnt, indid, id
FROM sys.sysindexes
WHERE id = OBJECT_ID('T1')

For the index on column (c2), you see a keycnt of 2. This is the key in the index plus the rowid.

For a nonclustered index on a clustered table, the row locator is the clustering key. Note, though, that if the clustered index is not defined as unique (PK, UQ etc), then another “uniqueifier” key/column is added. Building on above example:

CREATE UNIQUE CLUSTERED INDEX ix_T1_c1 ON T1 (c1) WITH DROP_EXISTING
SELECT name, keycnt, indid, id
FROM sys.sysindexes
WHERE id = OBJECT_ID('T1')
GO
CREATE CLUSTERED INDEX ix_T1_c1 ON T1 (c1) WITH DROP_EXISTING
SELECT name, keycnt, indid, id
FROM sys.sysindexes
WHERE id = OBJECT_ID('T1')

Consider the (non-clustered) index on column c2. For the first one, where the table has a unique clustered index, we see a keycnt of 2, the column c2 plus the clustered key. But when we define the clustered index as non-unique, we see +1 for the keycnt column; the uniqueifier. The uniqueifier is 4 byte, and only populated for rows which are duplicates of an existing clustered key (i.e. no extra cost if no duplicates).

But we want to stay away from compatibility views, right? Since we no longer have a key count column in sys.indexes, we need to grab that from sys.index_columns. This do not, however include the internal columns, only the explicitly defined columns:

SELECT
i.name
,i.index_id
,(SELECT COUNT(*)
FROM sys.index_columns AS ic
WHERE i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id) AS keycnt
FROM sys.indexes AS i
WHERE OBJECT_ID = OBJECT_ID('T1')

Share this post: on Twitter on Facebook on LinkedIn