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 |
|
lfmn
Posting Yak Master
141 Posts |
 Posted - 2002-01-09 : 09:50:15
|
| another developer on my project and I are having a debate about which is more efficient:where date between @date1 and @date2ORwhere date >= @date1and date <= @date2I'm voting for the first solution, but I don't really have any hard evidence to prove my point. Can anyone point me towards a resource which would help settle this debate?Thankscursors are like hammers - sometimes you have to use them, but watch your thumb! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-09 : 09:59:31
|
| Usually both syntaxes will generate the same plan, which technically makes them equivalent. (I vaguely remember reading that BETWEEN gets translated into the 2nd syntax anyway, but damn if I can remember where...it was a LOOOOOOOONG time ago) I think it's one of those things you'd have to extensively test and benchmark to know for certain. And if the performance is close, then use either one!If they don't generate the same plan, you would have to test various index types and/or index hints to see which is better. Personally I don't imagine that either one will show significant benefit over the other. |
 |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-01-09 : 10:18:31
|
| I think, but I am not sure, that if you use the BETWEEN statement, this allows for the query optimizer to better select and index on the table.*************************Just trying to get things done |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-09 : 10:31:32
|
| According to Inside SQL Server, BETWEEN is just a shorthand for ">= AND <=" construct. And from what I have seen, both notations are equivalent performance-wise. I like using BETWEEN though.Edited by - izaltsman on 01/09/2002 10:31:53 |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-01-09 : 16:37:13
|
| I have occasionally caught myself writing ad hoc queries saying things like "x BETWEEN '2000-04-01' AND '2001-03-31'" when I really meant "x >= '2000-04-01' AND x < '2001-04-01'"... |
|
|
|
nrafiq
Starting Member
9 Posts |
Posted - 2002-01-10 : 00:29:47
|
quote:
where date between @date1 and @date2ORwhere date >= @date1and date <= @date2
Hi,To specify an exclusive range, use the greater than (>) and less than operators (<). If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.Regards,Rafi |
|
|
|
|
|
|
|
|
Between debate