Do I need to INCLUDE the primary key in an index or not?

_{Thursday, June 6, 2013 12:27 PM}

Dear collegues,

The INCLUDE syntax in indexes is new to me. If I understand the online help correctly, every index will automatically include the clustered index. So if I have a table with a foreign key constraint and a datetime2 field, and if I know that including the (clustered) primary key and the datetime2 field in the index will help performance, do I need to include the primary key field in my code, or will it be done behind the scenes anyway?

To be a bit more precise, given the table definition below, do I need to create my index as in 1 or as in 2?

1. CREATE INDEX IX_NU_DossierAnalysis$DossierCode
   ON dbo.DossierAnalysis (DossierCode) INCLUDE (Id, CreDateTime);
2. CREATE INDEX IX_NU_DossierAnalysis$DossierCode
   ON dbo.DossierAnalysis (DossierCode) INCLUDE (CreDateTime);

CREATE TABLE dbo.DossierAnalysis
  ( Id              bigint      IDENTITY(1,1) NOT NULL
  , DossierCode int         NOT NULL
  , CreDateTime datetime2(3)    NOT NULL
  , TotalCost       numeric(12,2)   NOT NULL
  );
ALTER TABLE dbo.DossierAnalysis
  ADD CONSTRAINT PK_DossierAnalysis PRIMARY KEY CLUSTERED (Id);
GO

_{Thursday, June 6, 2013 12:31 PM ✅Answered}

Non-clustered indexes implicitly include the clustered index keys automatically.  So if your primary key is clustered (not a requirement), you do not need to explicitly include in non-clustered indexes.  The INCLUDE option is basically to facilitate creation of covering non-clustered indexes without adding columns to the key itself.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

_{Thursday, June 6, 2013 1:23 PM ✅Answered | 1 vote}

1. CREATE INDEX IX_NU_DossierAnalysis$DossierCode
   ON dbo.DossierAnalysis (DossierCode) INCLUDE (Id, CreDateTime);

Just for info: Even if you include Clustering Key column in NC Index definition it will be ignored by SQL Server. SQL Server is smart enough to know that a column already exits as part of clustered index key, so it will not duplicate this column in NC index.

- Vishal

SqlAndMe.com

_{Thursday, June 6, 2013 12:35 PM | 1 vote}

All non-clustered indexes will contain:

>> Clustering Key

>> Key column chosen for Index

_{Thursday, June 6, 2013 12:47 PM}

Thank you both. My PK is clustered and it is intentionally so. So I will not have to include it. Thanks again. I will use option 2.

_{Saturday, June 8, 2013 7:52 PM}

Ah, that is good to know. The primary key is clustered. I want the primary key to be included in the index. If at a later date, I would need another index to be the one that is clustered, because of the need for a covering index in certain queries, I still need the primary key to be included. The safest option seems to be to include it in the CREATE INDEX statement and let SQL Server decide if it will ignore it or not.

_{Sunday, June 9, 2013 5:20 PM}

All non-clustered indexes will contain:

>> Clustering Key

>> Key column chosen for Index

This is true for clustered tables; it is not true if the table is a heap.