ken.trock (12/6/2010)
Lowell (12/6/2010)
i think you'll just have to wrap a section of the query into a subquery, since the data has to be filtered on the LIKE statement first;because the second part uses a function (CONVERT)...it's not SARG-able, so a short circuit base don the index seeks cannot occur, right?
SELECT * FROM (
Select
Distinct RCD.Variable4
FROM Route_Call_Detail RCD
WHERE RCD.[DateTime] > '12/4/10'
AND RCD.[DateTime] < '12/5/10'
AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
) MyAlias
WHERE CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999
Makes sense but surprisingly it didn't change anything.
Ken
Heh, what I get for leaving the window open a bit. 🙂 Use OPTION (FORCE ORDER) at the tail of that, last piece of the statement.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA