Index SELECT MAX with WHERE

Question

I know that this question was asked many times, but I could not find an explicit answer when there is a WHERE clause as well, and this is very important for me since I will have table with Millions of records.

If I have:

SELECT MAX (SomeINTvalue)
FROM tbTest
WHERE Filter1 = SomeVARCHAR32value

What index is best to create?

Also, if I use:

SELECT TOP 1 
       SomeINTvalue
FROM tbTest
WHERE Filter1 = SomeVARCHAR32value
ORDER BY SomeINTvalue DESC

will it be better and what index to use in this case?

I researched many posts but I could not find an answer that considers WHERE clause (and these specific data types)

I suggest a non-clustered index on SomeVARCHAR32value, SomeINTvalue. Either query should perform well with this index. — Dan Guzman, Aug 08 '23 at 15:58

score 1 · Accepted Answer · answered Aug 08 '23 at 15:59

1

You need the following index:

CREATE NONCLUSTERED INDEX tbTest (SomeVARCHAR32value, SomeINTvalue DESC)

You may want to add INCLUDE columns also, depending on what other queries you are running.

Both queries will probably end up with the same plan if you have that index in place.

answered Aug 08 '23 at 15:59

Charlieface

52,284
6
19
43

I don't believe `DESC` is needed with the equality predicate on the leading column since the plan will include backward ordering for the seek predicate in the plan to touch the single row. – Dan Guzman Aug 08 '23 at 16:21
Just in case a parallel plan is being used in any other query: you can't do a parallel backwards scan. I seem to remember a cardinality estimation bug also if the index order isn't the same as the scan order. – Charlieface Aug 08 '23 at 16:26

Index SELECT MAX with WHERE

1 Answers1