Why SQL Server Tuning Advisor proposes to add PRIMARY KEY to included columns of index?

Question

I have seen this a number of times, when I run some analysis in SQL Server DataBase Engine Tuning Advisor it suggest me to create indexes like:

CREATE NONCLUSTERED INDEX [_index] ON [dbo].[SomeTable] 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC
)
INCLUDE ([PrimaryKeyColumn])

Does it really matter to include primary key (clustered index) column to included columns list? I always think that it is included by default as link to original row. Am I wrong?

Update: I think it is also important to note that it proposes such index for query like: SELECT [PrimaryKeyColumn] FROM [dbo].[SomeTable] WHERE ...[Conditions]... and it really influences performance and execution plan. So as far as I understand index doesn't contain really 'clustered index', but just some link to row. Is it so?

Primary Key and clustering index are not always 1-1. You should avoid conflating the two in your head. — Damien_The_Unbeliever, Feb 15 '12 at 09:38
@Damien_The_Unbeliever In this case PrimaryKeyColumn in clustered. That is what I wanted to point in description. Please let me know if I miss something. — Andrey Gulaev, Feb 15 '12 at 13:55

gbn · Accepted Answer · 2012-02-15T10:21:23.567

6

You can create the index with or without the INCLUDE: SQL Server will ignore it if the PrimaryKeyCol is the clustered index. That is, it won't store the clustered index value twice

For completeness, I probably would in case I ever change the clustered index

Edit:

I've observed via size that SQL Server deals with this intelligently
This is not as scientific as Kalen Delaney's More About Nonclustered Index Keys

DROP TABLE IncludeTest;
GO
CREATE TABLE IncludeTest (
    BadClusteredKey uniqueidentifier DEFAULT NEWID() PRIMARY KEY,
    OtherCol AS CHECKSUM(BadClusteredKey) % 10000,
    Filler char(200) NOT NULL DEFAULT 'a and lots of spaces'
    );
GO

INSERT IncludeTest (Filler) VALUES (DEFAULT);
GO
INSERT IncludeTest (Filler) SELECT Filler FROM IncludeTest
GO 20
SELECT COUNT(*) FROM IncludeTest;

EXEC sp_spaceused 'IncludeTest', 'true'
GO -- 400680 KB, 1920 KB

CREATE INDEX IX_OtherCol1 ON IncludeTest (OtherCol);
GO
EXEC sp_spaceused 'IncludeTest', 'true'
GO -- 400680 KB, 29024 KB KB

DROP INDEX IncludeTest.IX_OtherCol1
GO
EXEC sp_spaceused 'IncludeTest', 'true'
GO -- 400680 KB, 1920 KB

CREATE INDEX IX_OtherCol2 ON IncludeTest (OtherCol) INCLUDE (BadClusteredKey);
EXEC sp_spaceused 'IncludeTest', 'true'
GO -- 400680 KB, 29024 KB

DROP INDEX IncludeTest.IX_OtherCol2
GO
EXEC sp_spaceused 'IncludeTest', 'true'
GO -- 400680 KB, 1920 KB

edited Feb 15 '12 at 10:21

answered Feb 15 '12 at 10:08

gbn

422,506
82
585
676

+1 Now that is good to know. Do you have any references for that? – Lieven Keersmaekers Feb 15 '12 at 10:12
@Lieven [Kalen Delaney: More About Nonclustered Index Keys](http://sqlblog.com/blogs/kalen_delaney/archive/2010/03/07/more-about-nonclustered-index-keys.aspx). Also if the NCI is not unique the CI key will become part of the NCI key rather than just an included column. – Martin Smith Feb 15 '12 at 10:15
@Lieven: I'm just testing it, will add script shortly – gbn Feb 15 '12 at 10:15
@MartinSmith: thanks, I've always based this on size of index – gbn Feb 15 '12 at 10:21
@gbn Thank you for such good example with script. It still looks strange for as when I added Clustered Index Column to Included SQL Server changed execution plan for query, but currently I can't reproduce it any more. It may be something else influenced change. – Andrey Gulaev Feb 15 '12 at 14:00
After several days experiments I am certainly sure that although adding clustered index to non-clustered one may be not influencing performance directly, but it influences SQL execution plan for sure. I don't know why it is so, but SQL Server takes into account when it prepares execution plan. – Andrey Gulaev Feb 18 '12 at 10:00

score 0 · Answer 2 · answered Feb 15 '12 at 09:12

I think Tuning advisor detect you query and found that you have PrimaryKeyColumn in you select cause and you have Column1, Column2 and Column3 in where cause.

Tuning advisor think that.... if you index include PrimaryKeyColumn in the index SQL don't need to wast overhead to look up to get PrimaryKeyColumn data in data page(table data page). SQL engine would be able to take PrimaryKeyColumn value from index directly for you query. it save time..

Note that: There are bad side of the index(include column). at http://msdn.microsoft.com/en-us/library/ms190806.aspx contains useful information that might help you make decision.

Why SQL Server Tuning Advisor proposes to add PRIMARY KEY to included columns of index?

2 Answers2