Sql-server – Including clustered index key in non-clustered index

sql serversql-server-2008

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.

Best Answer

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.

Related Question