Do we need to include a clustered index in a non-clustered index?

Question

Do we need to include a clustered index in a non-clustered index?

christi1711

SSC-Addicted

Points: 441
More actions
January 29, 2021 at 11:16 am

#3838684

Hi all,
I have the Product table with such columns: ProductID, LastName, FirstName, DateDeleted.
We have two indexes on this table:
1. ProductID - Clustered
2.LastNameINCLUDE (FirstName).
Based on the statistics I saw that the nonclustered index is very effective for us. I have a lot of seeks. But the clustered index has a lot of lookups. I found a query that can use a lot of lookups (it runs about 1000 times per day):
SELECT ProductID, LastName, FirstName, FirstName
WHERE LastName = 'SomeValues' and DateDeleted IS NULL
That's why I decided to create one more nonclustered index:
LastName, DateDeleted INCLUDE(FirstName)WHERE (DateDeleted IS NULL)
I don't know should I put ProductID in this index? And whether this index will cover situations of the already existing non-clustered index. That is, whether it is possible to delete the first unclustered index

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16156 More actions · Answer 1

your issue is not the ProductID but the DateDeleted. This last field is what is causing the key lookups as it is not part of the second index (ProductID is part of the index as all non clustered indexes include the Clustered Index columns).

so you could potentially include DateDeleted on the second index - that would cover this particular query. and all others that use LastName as the filtering criteria.

christi1711 SSC-Addicted Points: 441 More actions · Answer 2

Thanks. But where will be better to include DateDeleted in the main part or in the INCLUDE condition?

1. LastName, DateDeleted INCLUDE(FirstName)WHERE (DateDeleted IS NULL)

2. LastName INCLUDE(FirstName, DateDeleted )WHERE (DateDeleted IS NULL)

And once again I wanted to clarify. I wrote a little wrong non-clustered index that I have. It is: LastNameINCLUDE (ProductID, FirstName). That's why I wanted to clarify. Is it important to add this clustered index to the INCLUDE? Can we skip it?

So will be these indexes mean the same?

LastName INCLUDE (ProductID, FirstName) and LastName INCLUDE (FirstName).

frederico_fonseca SSCoach Points: 16156 More actions · Answer 3

LastName INCLUDE (ProductID, FirstName) and LastName INCLUDE (FirstName) are the same in this case as ProductID is the clustered index "key".

regarding your indexes I misread your original post.

LastName, DateDeleted INCLUDE(FirstName)WHERE (DateDeleted IS NULL)

the above is a Filtered index - as such it does not require DateDeleted to be on the list of columns neither on the Include part.

it will only be used where your query contains "datedeleted is null" - will not be used in any other situation

depending on the size of that table (row count) and on the number of records that have DateDeleted set to null vs those that have it set to any value it may or not be useful to have it

for example if you have a table with 10 million rows and only 2k rows have it set to a value then the filtering is negligible and most likely it would be better to just have the column as a INCLUDE on the other index.

e.g. LastName INCLUDE (FirstName, DateDeleted)

christi1711 SSC-Addicted Points: 441 More actions · Answer 4

Thanks, I got it.

The whole table contains 29 million and when I use DateDeleted is NULL I have 12 million of records.

ScottPletcher SSC Guru Points: 101103 More actions · Answer 5

Make the nonclus index UNIQUE yourself, don't force SQL to do it for you. This is important for best performance.

And, yes, you should include the DateDeleted in the index. I know, SQL should "know" it's null (because of the filter), but I've had the same issue with SQL doing a lookup for just that column. [MS needs to make the SQL optimizer better on this at some point.]

UNIQUE ... ( LastName, ProductID ) INCLUDE ( DateDeleted, FirstName )

I always put the INCLUDEd columns in alpha order -- no reason not to, as it makes it faster later to verify if a certain column is INCLUDEd or not, and there's no downside to it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

christi1711 SSC-Addicted Points: 441 More actions · Answer 6

Scott, but why do you put the clustered index in the non-clustered? It is important?

Because when the first column in the select statement will be the clustered index and I didn't put it in the nonclustered index can I receive clustered index scan?

ScottPletcher SSC Guru Points: 101103 More actions · Answer 7

Yes, it is very important here, to ensure that the nonclus index key is unique.

And, besides, all clustered index key column(s) will always be added to every nonclus index whether you explicitly specify it or not.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".