Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We're running SQL Server 2008. In looking over the results of a query I ran on sys.dm_db_missing_index_* views, I see a curious suggestion for an included column. I have the following two tables:

create table foo (
    id int not null identity(1,1) primary key
    /* many more fields, not relevant to question */
)

create table bar (
    id int not null identity(1,1) primary key
    foo_id int not null,
    param_name nvarchar(50),
    param_value nvarchar(255)
)

The table foo has a 1:M relationship with bar; bar is used to store miscellany associated with some of the records in foo.

Back to the sys.dm_db_missing_index_* query. Several times a day, the optimizer is looking for an index on bar with foo_id in equality_columns and id in the included_columns. My question is, what is the point of including the table's clustered index key in a non-clustered index? Since the leaf level of the non-clustered index includes the clustered index's key value anyway, isn't it redundant to include the clustered index key in a non-clustered index?

EDIT: The only existing index on bar is the clustered index with the PK as the key.

Thanks in advance.

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

Yes, it is redundant.

On the other hand, it won't hurt anything (since the optimizer will ignore it and not add the data twice).

This is one of the many reasons not to take the suggested indexes at face value.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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