is is good
or doesn't matter
or bad
to INCLUDE the primary key in a covering index??
CREATE NONCLUSTERED INDEX index_name_here ON dbo.table_name_here
(column_to_index_here)
INCLUDE (primary_key_column,other_column_here)
WITH( STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, --<default junk from SSMS
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
I'm thinking it doesn't matter, since the PK will be in the index anyways.
EDIT - to clarify.
my primary_key_column
is clustered, but you can explain/give info when it isn't too.
I will be joining to dbo.table_name_here
on the column_to_index_here
column and will be then joining to other tables on primary_key_column
and other_column_here
.
If you are clustering on your PK, then it doesn't matter. SQL Server will disregard it since all non-clustered indexes contain the clustered index key for that row as part of their definition.
It won't use any extra space in the index, but it's redundant to include it in the definition.
If your PK isn't included in your clustered index, then only include it if you will need to retrieve that field as a part of the same query that uses the index.
Also bear in mind when you INCLUDE
a field in an index, it's NOT in the non-leaf nodes, i.e. the index is not sorted on that value.
since the PK will be in the index anyways.
Assuming you mean the PK is in another index with that statement, whether or not you include the primary key in the this index depends on if you are going to be selecting it in your queries. If you are going to select it, include it in the index, if not, leave it out. Also for table clustered on PK, see @JNK answer.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With