sql-servert-sqldatabase-indexessql-server-2022

Index SELECT MAX with WHERE

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)

Solution

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