Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pug2694328
Posting Yak Master
166 Posts |
 Posted - 2006-02-22 : 11:14:28
|
| I'm guessing BETWEEN is faster, but wonder if anyone has done any tests to confirm it.For example:SELECT NAMEFROM PRSNWHERE BIRTHDATE BETWEEN @DATE1 AND @DATE2or...SELECT NAMEFROM PRSNWHERE BIRTHDATE >= @DATE1AND BIRTHDATE <= @DATE2Thanks! |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-02-22 : 11:19:39
|
| I'll bet there's no difference at all.Look at the execution plans for verification. |
 |
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2006-02-22 : 11:45:06
|
| Thanks Sam, they both performed the same based on the execution plans. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-22 : 13:07:06
|
I always fret a bit about what value is the limit at the top end, and usually I want to do:WHERE BIRTHDATE >= @MyStartDate AND BIRTHDATE < @MidnightFollowingMyEndDate so BETWEEN doesn't suit me very well!!Kristen |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2006-02-22 : 13:18:23
|
| I'm confused, how is that different than <= ? My understanding was that BETWEEN actually includes the specified low and high end range values. Can you help me understand the situation that the < @MidnightFollowingMyEndDate accounts for? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-22 : 14:05:54
|
If I want everything from 01-Feb-2006 to 22-Feb-2006 then I need to include any values which have a date on 22-Feb-2006 AND anything with a TIME - up to midnight. So instead of<= '22-Feb-2006' (this will only select upto, and including, midnight between 21st/22nd Feb)I need to say< '23-Feb-2006' (This will select up to, but not including, midnight 22nd/23rd Feb, which is what I want  )Kristen |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2006-02-22 : 14:13:48
|
| Hey that makes sense! Thanks K. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-22 : 14:14:38
|
| "Hey that makes sense!"That's gotta be a first for me, ta!Kristen |
|
|
|
Tazz602
Starting Member
11 Posts |
Posted - 2006-02-22 : 15:13:53
|
| In answer to the original question - the DB engine most likely interprets between as >= and <= (I know that Oracle does it that way) - so if you really want faster - the later would be your answer but probably not where you would notice it. |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2006-02-24 : 13:42:25
|
| seems to be the case thanks Tazz. |
|
|
|
|
|
|
Which is faster, BETWEEN or <= and >= ?