Good index for DATE range search in MSSQL

Question

I have a table in Azure SQL Database that looks like this:

CREATE TABLE [dbo].[DayStatus](
    [Date] [DATE] NOT NULL,
    [LocationId] [INT] NOT NULL,
    [TypeId] [INT] NOT NULL,
    [Total] [DECIMAL](15, 5) NULL,
    [Timezone] [NVARCHAR](70) NULL,
    [Currency] [NVARCHAR](3) NOT NULL,

PRIMARY KEY CLUSTERED 
(
    [Date] ASC,
    [LocationId] ASC,
    [TypeId] ASC
)
) ON [PRIMARY]
GO

Question

I need to optimize the following SELECT statement to the above table:

SELECT 
    [Date],
    [LocationId],
    [TypeId],
    [Total],
    [Timezone],
    [Currency]
FROM [dbo].[DayStatus]

WHERE 
Date >= '2022-06-01' and Date <= '2023-01-17'
    and Currency = 'USD'
    and LocationId in (1, 2, 3, 4, 6, 10)
    and TypeId in (1, 2, 3, 5)

I have considered the following indexes, but it seems I cannot see a significant performance difference.

Which one is better, and is there an even better one?

Test 1

CREATE NONCLUSTERED INDEX [IX__Test1] ON [dbo].[DayStatus]
(
    [Date] ASC,
    [Currency] ASC,
    [LocationId] ASC,
    [TypeId] ASC
)
GO

Test 2

CREATE NONCLUSTERED INDEX [IX__Test2] ON [dbo].[DayStatus]
(
    [Currency] ASC,
    [LocationId] ASC,
    [TypeId] ASC
)
INCLUDE([Date],[Timezone],[Total])
GO

EDIT

Would it be better to have the query below?

SELECT 
    [Date],
    [LocationId],
    [TypeId],
    [Total],
    [Timezone],
    [Currency]
FROM [dbo].[DayStatus]

WHERE 
    Currency = 'USD'
    and LocationId in (1, 2, 3, 4, 6, 10)
    and TypeId in (1, 2, 3, 5)
    and Date >= '2022-06-01' and Date <= '2023-01-17'

Martin Smith · Accepted Answer · 2023-02-19T12:31:04.163

Equality columns should go before range columns.

Potentially the best indexing strategy would be

Any ordering of Currency, LocationId, TypeId as first three columns (choose whichever ordering is most useful for other queries in your workload and don't get hung up on selectivity here) followed by Date as the fourth key column and INCLUDE (Timezone, Total)

This would allow the query to return the results by UNION ALL-ing the results of 24 distinct index seeks (as the desired results are a concatenation of the following distinct ranges which can be seeked efficiently with such an index).

Currency = USD' and LocationId = 1 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 1 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 10 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 2 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 3 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 4 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
Currency = USD' and LocationId = 6 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'

I added Date as a fourth key column to your IX__Test2 index and the execution plan shows an index seek but digging into the properties shows SQL Server is doing the above.

Thanks! I have edited the question. Is the query rewrite you suggest the one in the edit of the question? — wetfield, Feb 19 '23 at 12:17
No reordering the `WHERE` clause won't change anything. I'll see if SQL Server does produce this plan with the original syntax or not anyway — Martin Smith, Feb 19 '23 at 12:24
@wetfield - no need for any query rewrite - with the suggested index it already does exactly that — Martin Smith, Feb 19 '23 at 12:28
Can you show me a screenshot of the execution plan? I get an execution plan with a Index Seek. — wetfield, Feb 19 '23 at 12:30
@wetfield - already in the answer. There is a single index seek operator but it has the 24 disjoint ranges as index seek predicates - in SSMS select the index seek operator and choose "Properties" from the context menu to see these details — Martin Smith, Feb 19 '23 at 12:31
This is probably a *very* stupid question, but why is querying the results by `UNION ALL` for the 24 different index seeks faster than the alternative (one index seek?) ? — wetfield, Feb 19 '23 at 13:06
The `UNION ALL` in this case is done through the index seek operator simply performing multiple distinct seeks rather than a separate concatenation operator. As for why seeking the exact ranges needed can be better it is because the seeks end up reading exactly the rows needed to return the result. There is no need for any residual predicate. In a phone book example If I want all Jack Smith, John Smith and Bill Smith I am probably best off doing three seeks on those exact names rather than a single seek on "Smith" and then read through all the Smiths and discard the others — Martin Smith, Feb 19 '23 at 13:12

Good index for DATE range search in MSSQL

1 Answers1