1

In SQL Server, What does the 'key' part of the phrase 'index key' mean? Is the key a number? what is it?

If I have the table of data containing a column called LastNames, and I have, say, a clustered index on it, is the 'key' for each row simply the last name? Would an example of a 'key value' be something like 'Cooper'?

I am asking because in non-clustered indexes on clustered tables, the leaf page of the non-clustered index contains the clustered index key. But what does it mean that it contains the index 'key'? Does this mean the actual column data stored on the leaf page of the clustered index (i.e. the data page) for the column that has a clustered index on it?

2
  • 1
    any time a row needs to be explicitly referenced in an index, it is referenced by the key column of the table the index references. If there is no explicitly defined key, a surrogate key, known as an RID in SQL Server, is referenced instead.
    – Hannah Vernon
    Mar 10, 2015 at 17:32
  • The key itself might be user-defined, as in when you create a surrogate key such as a sequence or identity, or natural key, such as your example of Cooper in a column LastName, or it might be a behind-the-scenes number used to uniquely identify each row in the table.
    – Hannah Vernon
    Mar 10, 2015 at 17:35

1 Answer 1

2

If we are talking about a currently supported version of Microsoft SQL Server, a non-clustered index does indeed contain a copy of the clustered index key.

This is one reason that there is emphasis on keeping keys as narrow as practical. Obviously a clustered key consisting of an integer would take much less space in the non-clustered index and therefore also require less I/O than if the key was varchar(128).

Note: If you have a clustered index that is "not unique", SQL Server will insert a hidden integer into the key so as to make it unique. By definition, a clustered index must be unique. This hidden number would also be included in the non-clustered indexes, of course.

If you are using some other implementation of SQL the details may be different.

EDIT: [Re question in comment} If you have: Clustered Index on column LastName and a non-clustered index on column FirstName, these are two separate indexes.

  1. The clustered index controls the sort order of the table, which contains all the data of the table. So, the clustered index has the clustered index key on LastName and the rest of the data in the table. The clustered index can provide the answer.

  2. The non-clustered index will have the non-clustered value for FirstName and the Cluster Key (which is LastName) in it. Depending on optimizer decisions which depend on more details of the query, this index may be used to satisfy the query.

1
  • Thanks for your post. So lets say I have a clustered index on a LastName column, and I have a non-clustered index on the FirstName column of the same table. Would an example the clustered index 'key' contained in the non-clustered index leaf-level page be something like 'Smith1'? Mar 11, 2015 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.