6

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.

9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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