Between Versus ">= and <="
-
Which method is more efficient?
(strDate between @startDate and @endDate)
or
(strDate >= @startDate and strDate <= @endDate)
Thanks!
Also, anyone know of a good resource for understanding and examining Exection Plans?
-
They're identical - less wordy with using "between"...
In fact, internally, SQL Server treats a "between" as ">= and <="..
Two ways of checking this..
1) look at the execution plan for both
2) try switching the dates in your "between" statement
eg: (strDate between @endDate and @startDate) - you won't get any results.
Check this site for articles on execution plans...here's one link:
**ASCII stupid question, get a stupid ANSI !!!** -
Thought I'd throw my 2 cents in here because lot of folks run into some real problems when using ranges of dates as criteria...
BETWEEN is fine for date ranges... until you have times on the dates (other than the normal 00:00:00.000 midnight time of a "date with no time")...
Let's say you want ALL of the rows from a table for the month of October 2006 and the table has a datetime column called "TheDate". If you can guarantee that all of the dates are without a time (actually, they would all have a time of 00:00:00.000), then BETWEEN works fine...
- SELECT *
FROM yourtable
WHERE TheDate BETWEEN '20061001' AND '20061031'
I don't EVER trust that though... unless there's a pretty good constraint on the column, someone always manages to sneak a non-midnight time in... there are a couple of ways you could handle it...
With BETWEEN and the fact that SQL Server has a 3 millisecond resolution on time, you could write your query this way...
SELECT *
FROM yourtable
WHERE TheDate BETWEEN '20061001' AND '20061031 23:59:59.997'
Now, if you are calculating dates, that gets kind of messy because you have to add the end-of-day time to the larger date. You could use the first day of the following month BUT then you can't use BETWEEN because midnight of that day (where most of the records "live" time-wise) would inherently be included. That means you need to do something like this...
SELECT *
FROM yourtable
WHERE TheDate >= '20061001'
AND TheDate < '20061101'
As Sushila pointed out (although slightly different), it'll be just as fast as using a BETWEEN without the hassle of having to embed an end-of-day time.
So, why not use some of the functionality built in the date/time functions of SQL? Something like (ANSI example)...
SELECT *
FROM yourtable
WHERE MONTH(TheDate) = 10
AND YEAR(TheDate) = 2006
The answer is because anytime you have a column name encapsulated in a function, the optimizer will simply refuse to use an INDEX SEEK that may be available on the column. ie. Performance problem. What if you don't have an index on the column and it's a 3rd party table you're not allowed to change? It's still a bad idea because 1) it's a bad habit to get into, 2) uses two functions where the other method uses none (performance difference only over millions of rows but still a bad habit), and 3) if the 3rd party ever does put an index on the column, you've guaranteed that your query won't be able to use it as an INDEX SEEK. The best it will ever do is an INDEX SCAN which is a fair bit (about 51 times) slower than a seek.
So, my recommendation is to NEVER use BETWEEN in conjunction with date-range criteria.
--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) - SELECT *
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply