BETWEEN keyword versus =

Question

BETWEEN keyword versus =

Paul McCurdy

Ten Centuries

Points: 1377
More actions
June 12, 2009 at 3:30 pm

#134998

I was surprised to learn from one of my colleagues that using
value1 <= value2 AND value1 >= value3
ran faster than
value1 BETWEEN value2 AND value3
I had always assumed they evaluated to the same thing in Optimizer. Not true? Or does it have someting to do with 32-bit vs 64-bit (this is on a 64-bit machine) or lack of indexes?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

Surely value1 = value3 would just evaluate to false and return no rows, assuming (value2 = value2 AND value1 <= value3 - in which case it by my understanding its exactly equivalent to the BETWEEN.

I'd be interested to see any examples where they generate different plans.

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 2

Stupid me - ypu're absolutely right!

value1 >= value2 AND value1 <= value3

was quicker by a few seconds than

value1 BETWEEN value2 AND value3

I know, not my understanding either and why I always use BETWEEN in all my SQL regardless of dB platform. But is there something different with SS2005?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Paul McCurdy (6/12/2009)
Stupid me - ypu're absolutely right!
value1 >= value2 AND value1 <= value3
was quicker by a few seconds than
value1 BETWEEN value2 AND value3
I know, not my understanding either and why I always use BETWEEN in all my SQL regardless of dB platform. But is there something different with SS2005?

The use of BETWEEN with dates that have times will screw you up pretty badly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 4

Jeff - so using datetime or smalldatetime with between will not work as well as >= and <= but will not matter if another type like int or char? Is that because of the time portion? And isn't datetime represented in SQLServer as a serial number?

Actually, since we have not yet upgraded to SS2008 where they finally implemented a DATE type, I often convert all my dates to CHAR(10),121 unless the time is important, which is infrequently.

Thanks so much for your input!

Tom257 SSChampion Points: 11543 More actions · Answer 5

I think what Jeff's getting at is doing a date range report, where the data you're querying has time a component.

So you want all June's Data - BETWEEN '2009-06-01' AND '2009-06-30'

you have a start and end parameter - the start parameter is OK, but the end parameter you probably supply as '2009-06-30'. But to include all June 30ths data you need to make the time 23:59:59.997 (point 997 as SQL Server accuracy isn't exact: point 999 will rollover to July 1st).

However its not that much of a problem - just write a function to take any date and return it as date plus the 23:59:59.997 and use the function in your betweens.

BETWEEN @StartDate AND dbo.fn_JustBeforeMidnight(@EndDate)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Tom Brown (6/13/2009)
I think what Jeff's getting at is doing a date range report, where the data you're querying has time a component.
So you want all June's Data - BETWEEN '2009-06-01' AND '2009-06-30'
you have a start and end parameter - the start parameter is OK, but the end parameter you probably supply as '2009-06-30'. But to include all June 30ths data you need to make the time 23:59:59.997 (point 997 as SQL Server accuracy isn't exact: point 999 will rollover to July 1st).
However its not that much of a problem - just write a function to take any date and return it as date plus the 23:59:59.997 and use the function in your betweens.
BETWEEN @StartDate AND dbo.fn_JustBeforeMidnight(@EndDate)

You're on the right track, Tom. But people that mess with the ol' 23:59:59.997 thing can end up with some surprises if they try to do that in 2008 on the new date types and you can't guarantee that someone won't change an existing column.

If you want all of the date for all dates in the range of '2009-06-01' AND '2009-06-30' the, as you say, the start date is just fine. But to account for all time anomolies, you really should add 1 to the end date and then do the following instead of BETWEEN...

AND somedatecol >= '2009-06-01'

AND somedatecol = StartDate and < NextStartDate

SELECT DATEADD(mm,t.Number, DATEADD(yy,@Year-1900,0)) AS StartDate,

DATEADD(mm,t.Number +1,DATEADD(yy,@Year-1900,0)) AS NextStartDate

FROM Master.dbo.spt_Values t

WHERE t.Type = 'P'

AND t.Number BETWEEN 0 AND 11[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)