Between vs. >= and <=

Which is better to use? I heard that <= and >= are better because it allows
SQL Server to retain the query plan in cache, or something like that. This i
s
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?http://www.aspfaq.com/2280
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
> Which is better to use? I heard that <= and >= are better because it
> allows
> SQL Server to retain the query plan in cache, or something like that. This
> is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <=
> <end_date>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>|||Is there any information on caching or query plan reuse? Basically, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?
"Aaron Bertrand [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2280
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
>
>|||I would think you would want to use DateDiff(), but that's just me.
Camps for Troubled Teens
http://www.brat-camps.com
Brat Camps - Free Search; Results Fast
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
Which is better to use? I heard that <= and >= are better because it allows
SQL Server to retain the query plan in cache, or something like that. This
is
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?|||I have provided a example of why using datediff is not a correct solution in
this example.
Using between (as you will see in query anyliser) it will do an index s,
using the datediff method causes it to ignore the index and scan the full
table.
IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates
CREATE TABLE #tmpDates ( myDate DATETIME PRIMARY KEY CLUSTERED )
INSERT INTO
#tmpDates
SELECT
DATEADD( d , -n , GETDATE() ) AS myDate
FROM
tblNumbers
WHERE
N BETWEEN 1 AND 20
/* uses indexed s */
SELECT
*
FROM
#tmpDates
WHERE
myDate BETWEEN {d '2005-09-22'} AND DATEADD( ms , -3 , {d '2005-09-29'} )
/* forces scan */
SELECT
*
FROM
#tmpDates
WHERE
DATEDIFF( d , {d '2005-09-22'} , myDate ) >= 0
AND
DATEDIFF( d , myDate , {d '2005-09-28'} ) >= 0
"Brat Camps" <spam at diamondflex dot com> wrote in message
news:ONvyf30yFHA.916@.TK2MSFTNGP10.phx.gbl...
> I would think you would want to use DateDiff(), but that's just me.
> --
> Camps for Troubled Teens
> http://www.brat-camps.com
> Brat Camps - Free Search; Results Fast
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
> Which is better to use? I heard that <= and >= are better because it
allows
> SQL Server to retain the query plan in cache, or something like that. This
> is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <=
<end_date>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>
>|||Hammer that box! Woo Hoo!!!
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Rebecca York" wrote:

> I have provided a example of why using datediff is not a correct solution
in
> this example.
> Using between (as you will see in query anyliser) it will do an index s
,
> using the datediff method causes it to ignore the index and scan the full
> table.
>
> IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates
> CREATE TABLE #tmpDates ( myDate DATETIME PRIMARY KEY CLUSTERED )
> INSERT INTO
> #tmpDates
> SELECT
> DATEADD( d , -n , GETDATE() ) AS myDate
> FROM
> tblNumbers
> WHERE
> N BETWEEN 1 AND 20
> /* uses indexed s */
> SELECT
> *
> FROM
> #tmpDates
> WHERE
> myDate BETWEEN {d '2005-09-22'} AND DATEADD( ms , -3 , {d '2005-09-29'} )
> /* forces scan */
> SELECT
> *
> FROM
> #tmpDates
> WHERE
> DATEDIFF( d , {d '2005-09-22'} , myDate ) >= 0
> AND
> DATEDIFF( d , myDate , {d '2005-09-28'} ) >= 0
>
>
> "Brat Camps" <spam at diamondflex dot com> wrote in message
> news:ONvyf30yFHA.916@.TK2MSFTNGP10.phx.gbl...
> allows
> <end_date>
>
>|||Is there any information on caching or query plan reuse? Basically, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?
"John Barr" wrote:

> Which is better to use? I heard that <= and >= are better because it allow
s
> SQL Server to retain the query plan in cache, or something like that. This
is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date
>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>|||On Fri, 7 Oct 2005 07:44:04 -0700, John Barr wrote:

>Is there any information on caching or query plan reuse? Basically, I am
>looking for information pertaining to the fact that SQL Server converts a
>Between to a >= and <= pair, and if you resubmit the same between statement
,
>it converts it again and regenerates it plan. If you use the >= and <=
>instead, it will reuse the existing plan to re-execute the statement. Is th
is
>true? and if so, is there documentation on it?
Hi John,
This is not true. It would be quite silly if MS had overlooked this - if
each proc that uses a BETWEEN is recompiled on each execution, DBA's
would be screaming and shouting at MS.
To test this, simple start Profiler, run the following code, then check
for any SP:Recompile events in the profiler trace.
use pubs
go
create proc dbo.test
as
select * from authors where au_fname between 'A' and 'C'
go
exec dbo.test
go
exec dbo.test
go
exec dbo.test
go
drop proc dbo.test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||John
If you the BETWEEN, and you have a usable index so that the plan can be
autoparameterized, you will see that the cached plan in syscacheobjects has
already converted it to use the >= and <=
So then running the query with the >= and <= will use the already cached
plan. No new plan will need to be created.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:0961B42D-005E-4AEB-8143-97472DF54BBB@.microsoft.com...
> Is there any information on caching or query plan reuse? Basically, I am
> looking for information pertaining to the fact that SQL Server converts a
> Between to a >= and <= pair, and if you resubmit the same between
> statement,
> it converts it again and regenerates it plan. If you use the >= and <=
> instead, it will reuse the existing plan to re-execute the statement. Is
> this
> true? and if so, is there documentation on it?
>
> "John Barr" wrote:
>
>|||Since they compile the same way, ask yourself which form gives you
easier to read and maintain code. You will pick BETWEEN for the poor
guy that comes after you.