[thelist] SQL: BETWEEN versus (Greater Than and Less Than)
r937
rudy at r937.com
Fri Mar 23 10:58:11 CDT 2007
sorry i missed the earlier excitement in this thread
performing a function on a table column and comparing the result to a static
value is a sure way to force the optimizer into table scans
put the column on the left side of the operator, and all the calculations
(if necessary) on the right
(the "if necessary" comment refers to constructions like
DATEADD(day,1,GETDATE()) to represent tomorrow -- obviously, in a stored
proc you'd pre-calculate the value)
jason had the right idea with this --
where create_dt >= convert(DATETIME, '20070319')
and create_dt < convert(DATETIME, '20070320')
but sql server is smart enough to recognize date strings, so you really
don't need to do an explicit conversion ...
where create_dt >= '20070319'
and create_dt < '20070320'
using "greater than or equal" together with "less than" makes it a half-open
interval, which is the easiest way to construct the range when datetime
values (rather than date only) are involved
rudy
http://r937.com/
More information about the thelist
mailing list