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.
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.
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.
Cooper
in a columnLastName
, or it might be a behind-the-scenes number used to uniquely identify each row in the table.