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 |
|
obiwaugh
Starting Member
27 Posts |
 Posted - 2005-03-16 : 13:12:52
|
| Is there any difference in performance when using BETWEEN with date ranges or using a combination of > and < than statements.--------------------------------I know enough to know that I don't know enough. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 13:16:29
|
| Almost certainly not, but as a point of "style" I prefer separate >= or > and <= or < statementsKristen |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-16 : 13:18:44
|
run your query with between and look at the execution plan.you'll see thatwhere col1 between 5 and 10translates to where col1 >= 5 and col1 <= 10Go with the flow & have fun! Else fight the flow  |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-16 : 20:42:41
|
| Using BETWEEN can sometimes let the optimizer choose an optimization that >=/<= would not, but it's very dependent on the indexes and statistics that are available. Otherwise, it works as spirit describes, and you don't lose anything. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-16 : 20:53:46
|
| Taking your initial question literally, be sure to realize that BETWEEN is equivalent of >= and <=, as opposed to > and <.Just making sure.- Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-17 : 04:39:08
|
| I think Jeff's point is particularly relevant to adopting a style of ">" and "<" (with or without "=") for clarity.We often need to do date ranges like:WHERE MyDate >= '01-Jan-2005' AND MyDate < '02-Jan-2005'to catch all timestamps on the 01-Jan, but exclude anything bang-on midnight of the 02-Jan.Kristen |
|
|
|
|
|
|
BETWEEN function