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.