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?
Best Answer
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 wasvarchar(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 columnLastName
and anon-clustered index
on columnFirstName
, these are two separate indexes.The
clustered index
controls the sort order of the table, which contains all the data of the table. So, theclustered index
has theclustered index key
onLastName
and the rest of the data in the table. Theclustered index
can provide the answer.The
non-clustered index
will have the non-clustered value forFirstName
and theCluster Key
(which isLastName
) in it. Depending on optimizer decisions which depend on more details of the query, this index may be used to satisfy the query.