SQL Server Performance Forum – Threads Archive

Rewrite of suppressed index query

I have the following query that needs to be rewritten (if possible) so that the clustered index on column "coldate" can be utilized. Any suggestions would be appreciated. SELECT *
FROM
table t
WHERE
DateDiff(n, DateAdd(n, t.col1 – 1, t.coldate), @date) <= 0
AND
DateDiff(n, t.coldate, DateAdd(n, @days, @date)) > 0

Just avoid using functions on the column.
Example change : DateDiff(n, t.coldate, DateAdd(n, @days, @date)) > 0 to t.coldate< dateadd(n, @days, @date) KH

So I did as you said and revised my query to: SELECT * FROM table t WHERE DateDiff(n, DateAdd(n, t.col1 – 1, t.coldate), @date) <= 0 AND t.coldate < dateadd(n, @days, @date) The original statement resulted with a Clustered Index Scan with 360 logical reads. The revised statement resulted in a Clustered Index Seek with 69 logical reads. So my question now…from what I have read, it seems like I should have still received a Clustered Index Scan, because the first part of the WHERE clause is using a function [DateDiff] on an indexed column. My explain plain from the revised statement is; |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[MyDB].[dbo].[Table].[IX_Table_ColDate]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Table].[ColDate] < dateadd(minute, Convert([@days]), [@date])), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />datediff(minute, dateadd(minute, Convert([Table].[Col1])-1, [Table].[ColDate]), [@date])<=0) ORDERED FORWARD) I do not want to argue with success, and there is room for improvement, but with that aside, why is there not an Index Scan on the DateDiff function used on an indexed column?

]]>