CODEWITHSUNDEEP
sql-servert-sql
user3284707
user3284707

Reputation: 3341

How to correctly include the right columns in a SQL index

I am struggling to get the correct index for a small query I want to run. I was wondering if anyone had any tips for this kind of query?

My table columns are:

Id int (not used in query) PK
DateField datetime
IntField1 int
IntField2 int
IntField3 int
IntField4 int
IntField5 int (Not used in query)
IntField6 int (Not used in query)

My query looks like this

DECLARE @datefield datetime = '2019-01-01';
DECLARE @intfield1 int 1;
DECLARE @intfield2 int 2;

SELECT
SUM([sum])
FROM table1
WHERE 
DATEPART(YEAR, [DateField]) = DATEPART(YEAR, @datefield)
AND DATEPART(MONTH, [DateField]) = DATEPART(MONTH, @datefield)
AND [IntField1] = @intfield1
AND [IntField2] = @intfield2
AND [IntField3] = [IntField4]

The best index I have found on the execution plan is one that looks like this

CREATE INDEX idx_test_1 ON table1([DateField], [IntField1])

I have also tried something like this...

CREATE INDEX idx_test_2 ON table1([DateField], [IntField1], [IntField2], [IntField3], [IntField4])

But this turned out worse in the execution plan

I've been trying to follow this guide, but any other help would be appreciated.

https://www.sqlshack.com/sql-server-index-design-basics-and-guidelines/

Here is the execution plan for the query

https://www.brentozar.com/pastetheplan/?id=SyW3LQk5r

Upvotes: 0

Views: 58

Answers (1)

PeterHe
PeterHe

Reputation: 2766

The function on the [DFateField] makes the index useless. You need to change the condition:

DATEPART(YEAR, [DateField]) = DATEPART(YEAR, @datefield)
AND DATEPART(MONTH, [DateField]) = DATEPART(MONTH, @datefield)

You need to rewrite your query to compare the field [DateField] direct with the parameters.

Both indexes should work. But depends on the selectivity, test1 index may be good enough.

You can calculate the date range for the month:

DECLARE @start_month date;
DECLARE @end_month date;

SET @start_month=DATEADD(DAY,1-DATEPART(DAY, @datefield),@datefield);
SET @end_month=DATEADD(MONTH,1,@start_month);

Then change the above condition to:

[DateField] >= @start_month  AND [DateField] < @end_month

Upvotes: 1

Related Questions