[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


More information about the thelist mailing list