THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

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 INTc2 datetimec3 VARCHAR(3))
CREATE INDEX ix_T1_c1 ON T1 (c1)
CREATE INDEX ix_T1_c1_c2 ON T1 (c1c2)
CREATE INDEX ix_T1_c1_c2_c3 ON T1 (c1c2c3)
CREATE INDEX ix_T1_c2 ON T1 (c2)
SELECT namekeycntindidid
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 (c1WITH DROP_EXISTING
SELECT namekeycntindidid
FROM sys.sysindexes
WHERE id OBJECT_ID('T1')
GO
CREATE CLUSTERED INDEX ix_T1_c1 ON T1 (c1WITH DROP_EXISTING
SELECT namekeycntindidid
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_idAS keycnt
FROM sys.indexes AS i
WHERE OBJECT_ID OBJECT_ID('T1')
Published Tuesday, June 23, 2009 9:57 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Christian Wirth said:

Very helpful! Thank you!

June 23, 2009 8:48 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement