3

Lets assume we have a table as follows:

PK -- nonclustered primary key
Col1 -- unique clustered
Col2
Col3

Is this index:

nonclustered index on (Col2, Col3)

any different from this index:

nonclustered index on (Col2, Col3) include (PK)

What if my PK is clustered instead of nonclustered? Now does it make/not make a difference?

Edit to add:

I suppose this is a round-about way of asking: Is the nonclustered index's pointer back to the table based on the clustered index or the primary key? I assume the clustered index RID is what is used.

Improve this question
6
  • For your table the Col1 column will be added to each index (so a lookup can get back to the actual row, and why small clustered keys are better than wide ones), so the include (PK) will make a difference, because it is not the clustered index.
    – KM.
    Commented Aug 5, 2011 at 14:30
  • This is why I asked - Leons mentions its always included if it's clustered, you say always. I'm not sure which is correct!
    – Derek
    Commented Aug 5, 2011 at 14:35
  • 1
    By reading this msdn.microsoft.com/en-us/library/ms177484.aspx I would say that the nonclustered index has a pointer back to the clustered index. The primary key is not stored in the nonclustered index unless the PK and the clustered index is the same.
    – Mikael Eriksson
    Commented Aug 5, 2011 at 14:42
  • The clustered index is always included in every non-clustered index. Not the primary key (unless that is the clustered index). If it was the primary key, what use would a non-clustered index on the primary key be? And how would you then find the non-indexed columns using it?
    – Damien_The_Unbeliever
    Commented Aug 5, 2011 at 14:43
  • @Derek Kromm, I've edited my answer to reflect your given table
    – KM.
    Commented Aug 5, 2011 at 14:44

3 Answers 3

Reset to default
9

The clustered index key is included in every non-clustered index as the row identifier.

SQL Server just ignores an INCLUDE of the clustered index key.

If your PK index is NONCLUSTERED then it will not be included in every row of all other NC indexes.

You can test this yourself by making two identical indexes, one with INCLUDE(ClusterKey) and one without, and compare the sizes. They will be identical, even on hundreds of millions of rows.

Improve this answer
2

Imagine if each non-clustered index contained the primary key, as you suppose:

This would, based on your table, presumably include the non-clustered index that's implementing your primary key. Imagine that you're performing a query against the table, and the primary key index is used. There's no way, having used this index, to find the remaining data for the table. So that couldn't possibly be the way things work.

Or another hypothesis:

Every non-clustered index that isn't the primary key index stores the primary key. If the primary key index is non-clustered, then it contains some magical other value (e.g. the clustered key). But that then means, than any query that uses a non-clustered, non-primary key index must now perform twice as many index operations - once against it's own index, and a second one against the primary key index. And we'd also need two separate implementations of non-clustered indexes.

Since neither of these are a good fit, it's hopefully obvious why non-clustered indexes store the clustered index key, rather than anything else.

Improve this answer
1

If the PK is a clustered PK, then the PK will be included in every index that you create, except for a unique nonclustered index. If you need the PK column on the unique nonclustered index, then you will still need to include the column.

Improve this answer
3
  • 3
    What do you mean by "except for a unique index"? the PK will still be included in the index right? It just won't participate in the uniqueness constraint...?
    – Tao
    Commented Aug 5, 2011 at 14:36
  • @Tao - My answer was not clear - I should have said "Except for a unique nonclustered index". A unique nonclustered index will not contain the clustered key, resulting in an additional lookup. This can be verified via the explain plan. I will edit my post to indicate that this applies only to this case.
    – Leons
    Commented Aug 5, 2011 at 15:41
  • OK, after correction of @KM's answer I regret my use of the term "PK" - I meant clustered index key, which in OP's case is an entirely different column. I still don't understand what you mean by the PK not being included in a unique nonclustered index though: it definitely WILL be included, if it is a clustered PK!
    – Tao
    Commented Aug 5, 2011 at 15:58

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.