CODEWITHSUNDEEP
sql-server
Lars Bremer
Lars Bremer

Reputation: 31

MSSQL index between two dates

I have a table that consist of many columns, among them two dates (startDate and endDate).

The table contains 15.000.000 records.

My query is is setup like this

SELECT ID FROM table WHERE @inputdate BETWEEN startDate and endDate

How should my index be setup to maximize performance?

Upvotes: 3

Views: 3332

Answers (1)

jean
jean

Reputation: 4350

To extend my comment to an answer.

CREATE UNIQUE NONCLUSTERED INDEX
[UIX_TABLE_StartDateEndDate]
 ON [dbo].[TABLE]
 ([StartDate] ASC, [EndDate] ASC)
 INCLUDE([ID])

Is a good start. To maximize performance you must profile the load and maybe you can improve it with minor tweeks like setting fillfactor padding etc.

Another good choose from start is if your date columns are nullable and you never search for the null values. That way you can apply a filter to the index like

CREATE UNIQUE NONCLUSTERED INDEX
    [UIX_TABLE_StartDateEndDate]
     ON [dbo].[TABLE]
     ([StartDate] ASC, [EndDate] ASC)
     INCLUDE([ID])
WHERE ([StartDate] IS NOT NULL AND [EndDate] IS NOT NULL)

Upvotes: 2

Related Questions