[SQL Server 2008] Problem with Between syntax

Question

[SQL Server 2008] Problem with Between syntax

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Jason A. Long (5/15/2015)
WOW... I could have sworn that the last time I tested, CASTing from a DATETIME to a DATE made it unsargable...
Just tetested with this...
DECLARE
@BegDate DATE = '2015-01-10',
@EndDate DATE = '2015-05-15'
SELECT
tt.DateAndTime
FROM dbo.TestTime tt
WHERE
tt.DateAndTime >= @BegDate
AND tt.DateAndTime < @EndDate
SELECT
tt.DateAndTime
FROM dbo.TestTime tt
WHERE
CAST(tt.DateAndTime AS DATE) BETWEEN @BegDate AND @EndDate
They both produced the exact same execution plan... I'm feeling like a moron... So I'll shut up now (and take it off the "never" list Monday morning)... :Whistling:

Although that may work as you say, I'd still say "Never" simply because it's not needed at all if you do the >=/< and will never be mistaken as non-sargable code by others. It also makes it easier to determine other non-sargable code.

Of course, all that is just my opinion and recommendation.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Lynn Pettis (5/15/2015)
Sometimes using a function does NOT mean the comparision becomes nonSARGable.

While that may be true, I'll avoid it whenever possible.

--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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

Jeff Moden (5/15/2015)
Jason A. Long (5/15/2015)
ScottPletcher (5/15/2015)
Never use BETWEEN on dates or datetimes;
Out of curiosity, why do you say this?
I could see it it you were talking about DATETIME data types only, simply because of the logical "<=" on the EndDateTime, but I don't see how it's an issue with the DATE data type...
I can't speak for Scott but my answer would be, why do it two different ways when 1 way will work very well for both. I've also seen it when people finally make the realization of the huge mistake they made when they separated DATE and TIME into separate columns and recombine them further down the line. That, of course, means that they have to go back and find all of the BETWEEN code and change it to the >=/< code. It's much safer just to stay in that habit to begin with.

Exactly. Also, column types can changes. And Feb can easily be miscoded otherwise to leave off the leap day.

Besides, how do you explain to developers the shop standards for coding this. "The preferred way to code data comparisons is to >= and < rather than BETWEEN, but BETWEEN is ok in situations where it won't adversely affect the query plan." They'd spend more time verifying that it wouldn't hurt the query plan than just coding it the better way to begin with.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

I agree that the ">= / <" syntax is the all around better syntax and I'll stick to it exclusively, moving forward.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

Jason A. Long (5/15/2015)
WOW... I could have sworn that the last time I tested, CASTing from a DATETIME to a DATE made it unsargable...
Just tetested with this...
DECLARE
@BegDate DATE = '2015-01-10',
@EndDate DATE = '2015-05-15'
SELECT
tt.DateAndTime
FROM dbo.TestTime tt
WHERE
tt.DateAndTime >= @BegDate
AND tt.DateAndTime < @EndDate
SELECT
tt.DateAndTime
FROM dbo.TestTime tt
WHERE
CAST(tt.DateAndTime AS DATE) BETWEEN @BegDate AND @EndDate
They both produced the exact same execution plan... I'm feeling like a moron... So I'll shut up now (and take it off the "never" list Monday morning)... :Whistling:

Don't feel bad. MS actually changed the optimizer to better "sarg" certain temporal data. I guess people were coding it unsargable so often they decided to have the engine compensate for it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

cms9651 SSCommitted Points: 1940 More actions · Answer 6

cms9651

SSCommitted

Points: 1940

May 19, 2015 at 2:03 am

#1798914

Thank you very much for help.