good/doesn't matter/bad to include Primary Key in covering index?

Sql ServerTsql

Sql Server Problem Overview


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.

Sql Server Solutions


Solution 1 - Sql Server

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.

Solution 2 - Sql Server

> 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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionRacerXView Question on Stackoverflow
Solution 1 - Sql ServerJNKView Answer on Stackoverflow
Solution 2 - Sql ServerBlackICEView Answer on Stackoverflow