April 20, 2012 at 11:08 am
I am probably phrasing this wrong (and someone will gently tap me upside the head with the pork chop launcher to correct me)...
From what I've seen, SQL has a tendency to pre-calculate function and subquery results before it gets to the rest of the code. From the moment you hit the execute button, one thread is off and running for that DATEADD() function and ignoring the rest of the WHERE clause as it caches all the handy data for comparing to the value on the other side of the equal sign. When it runs out of cache or hits the break-it point of a value, SQL fails the query leaving you to figure out where in the process it bombed.
And, ISTR, SQL 2000 was not very good at juggling all the query threads the way that SQL 2005 and 2008 are. There's a reason so much of the engine got rebuilt for 2k5. 2k would have silicon embolisms trying to keep up with some of the functionality that we now take for granted.
April 20, 2012 at 11:31 am
The original version fails because it has to do the DateAdd on impossible numbers before it can compare it to the final date. There's simply no way for it to tell that "6000" (for example) is a valid row until it does the date math on it, and then it fails at that step.
The DateDiff version skips that by calculating a number first and then comparing numbers to numbers. Hence no error.
The only reason it didn't fail in SQL 2008 is because of a smaller "Tally" table range.
Just ran this in SQL 2008 R2 Dev Edition:
USE ProofOfConcept
GO
CREATE TABLE dbo.BigNumbers
(Number INT PRIMARY KEY) ;
GO
;
WITH Seeds(Seed)
AS (SELECT R
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (R)),
Numbers(Number)
AS (SELECT ROW_NUMBER() OVER (ORDER BY S1.Seed)
FROM Seeds AS S1
CROSS JOIN Seeds AS S2
CROSS JOIN Seeds AS S3
CROSS JOIN Seeds AS S4
CROSS JOIN Seeds AS S5
CROSS JOIN Seeds AS S6)
INSERT INTO dbo.BigNumbers
(Number)
SELECT TOP 65535
number
FROM Numbers ;
GO
DECLARE @FREQ INT
SET @FREQ = 5
SELECT DATEADD(MM, @FREQ * NUMBER, '20120501') AS MYDATE,
NUMBER
FROM dbo.BigNumbers
WHERE DATEADD(MM, @FREQ * NUMBER, '20120501') < '20200501';
Error:
Msg 517, Level 16, State 1, Line 4
Adding a value to a 'datetime' column caused an overflow.
So it's not an SQL 2000 thing, it's an SQL thing, and it has to do with the query mechanics.
No way around it, you'll need to modify the query to work, or use a smaller Tally/Numbers table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 20, 2012 at 7:18 pm
What Gsquared is saying is right, but it may be easier to understand with a slightly different emphasis.
If you look at the where condition it's
DATEADD(MM, @FREQ*NUMBER, '20120501') < '20200501'
NUMBER is involved in a fairly complicated expression, which clearly isn't SARGABLE because SQL Server can't work out what condition it can apply to NUMBER as opposed to of some function of NUMBER there is here. If it understood that the function used here always had a positive first drivative (in the discrete sense, since the functions here are not continuous) and could use that information it could convert the test to a direct test on the value of NUMBER, it could perhaps discover that the condition was equivalent to NUMBER < xxxx (whatver xxxx is - I can't be bothered to work it out) and use that condition, which would allow it never to test with numbers that wouldn't fit. Unfortunately (or perhaps not - maybe if it was that clever almost everything would run very slowly while it excercised it's cleverness, and only a few things like this would switch from breaking to running or from running slower to running faster) it isn't that clever, so it has to test all values of number and that means it will break.
Tom
April 21, 2012 at 3:52 am
This is interesting.
So out of these which would result in a calculation for ALL numbers in the tally table and is there a way of telling?
WHERE NUMBER <= DATEDIFF(M,#FIRSTDATE,#SECONDDATE)
WHERE NUMBER <= 10
WHERE DATEADD(M, NUMBER, #FIRSTDATE) <= #SECONDDATE
We know the third one does already as it raises an error, if the first one does as well it appears you can't minimise the rows from the tally table that it tests using the where clause.
What I am trying to get at is if it is possible to prevent this happening using the where clause or a derived table Instead of having to create different sized tally tables.
Rolf
April 21, 2012 at 4:16 am
kangarolf (4/21/2012)
This is interesting.So out of these which would result in a calculation for ALL numbers in the tally table and is there a way of telling?
WHERE NUMBER <= DATEDIFF(M,#FIRSTDATE,#SECONDDATE)
WHERE NUMBER <= 10
WHERE DATEADD(M, NUMBER, #FIRSTDATE) <= #SECONDDATE
We know the third one does already as it raises an error, if the first one does as well it appears you can't minimise the rows from the tally table that it tests using the where clause.
What I am trying to get at is if it is possible to prevent this happening using the where clause or a derived table Instead of having to create different sized tally tables.
Rolf
The first and second are OK, they will only look at the rows which match the condition (assuming your '#' is '@', of course, so that these are not interpreted as temporary tables or procs - otherwise you get some nice syntax errors) unless @firstdate and @lastdate are so far apart that the hit range is suffciently close to teh whole table that the optimiser thinks it will be more efficient not to use the index on NUMBER in the tally table.
Tom
April 23, 2012 at 5:06 am
What you might consider is either a subquery, CTE, or TempTable for your initial Tally Table pull. Pull just the numbers from the Tally Table that you need and only then do your other WHERE clause.
For instance:
DECLARE @FREQ INT
SET @FREQ = 2
WITH InitialTally AS
(SELECT Number FROM UTILITY..Numbers WHERE Number < 10)
SELECT DATEADD(MM, @FREQ* n.NUMBER, '01/05/2012') AS MYDATE, n.NUMBER
FROM UTILITY..NUMBERS n
INNER JOIN InitialTally it1
ON n.Number = it1.Number
WHERE DATEADD(MM, @FREQ*n.NUMBER, '01/05/2012') < '01/05/2020'
Note: I have not tested this for this particular scenario. It has, however, worked for me on other occasions with other data.
April 23, 2012 at 6:37 am
L' Eomot Inversé (4/21/2012)
kangarolf (4/21/2012)
This is interesting.So out of these which would result in a calculation for ALL numbers in the tally table and is there a way of telling?
WHERE NUMBER <= DATEDIFF(M,#FIRSTDATE,#SECONDDATE)
WHERE NUMBER <= 10
WHERE DATEADD(M, NUMBER, #FIRSTDATE) <= #SECONDDATE
We know the third one does already as it raises an error, if the first one does as well it appears you can't minimise the rows from the tally table that it tests using the where clause.
What I am trying to get at is if it is possible to prevent this happening using the where clause or a derived table Instead of having to create different sized tally tables.
Rolf
The first and second are OK, they will only look at the rows which match the condition (assuming your '#' is '@', of course, so that these are not interpreted as temporary tables or procs - otherwise you get some nice syntax errors) unless @firstdate and @lastdate are so far apart that the hit range is suffciently close to teh whole table that the optimiser thinks it will be more efficient not to use the index on NUMBER in the tally table.
Won't matter if the range is too large. Try it. The only way to get an overflow error on the date computation in this case would be to assign a date to one of the Date/DateTime variables that was out of range. And then the error would be very, very easy to trace and debug.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 6:38 am
Brandie Tarvin (4/23/2012)
What you might consider is either a subquery, CTE, or TempTable for your initial Tally Table pull. Pull just the numbers from the Tally Table that you need and only then do your other WHERE clause.For instance:
DECLARE @FREQ INT
SET @FREQ = 2
WITH InitialTally AS
(SELECT Number FROM UTILITY..Numbers WHERE Number < 10)
SELECT DATEADD(MM, @FREQ* n.NUMBER, '01/05/2012') AS MYDATE, n.NUMBER
FROM UTILITY..NUMBERS n
INNER JOIN InitialTally it1
ON n.Number = it1.Number
WHERE DATEADD(MM, @FREQ*n.NUMBER, '01/05/2012') < '01/05/2020'
Note: I have not tested this for this particular scenario. It has, however, worked for me on other occasions with other data.
Won't work in this case. The whole point of the query is getting all dates in a range of unknown scope (unknown at coding time, known only at runtime).
That's why a DateDiff version of the Where clause is the right solution here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2012 at 7:34 am
--Pleas, compare this two scripts:
---------------------------------
DECLARE @FREQ INT
DECLARE @NUMBER INT
SET @FREQ = 5
SET @NUMBER =10000
BEGIN
IF DATEADD(MM, @FREQ*@NUMBER, '20120501') > '20200501'
SELECT 'O.K.'
ELSE
SELECT 'DateTimeERROR'
END
----------------------------------------------------------------
DECLARE @FREQ INT
DECLARE @NUMBER INT
SET @FREQ = 5
SET @NUMBER =100000 -- excesive value is the cause of error --
BEGIN
IF DATEADD(MM, @FREQ*@NUMBER, '20120501') > '20200501'
SELECT 'O.K.'
ELSE
SELECT 'DateTimeERROR'
END
April 24, 2012 at 9:00 am
Hey, what's up !
It looks like you can only add until 95851 months to a date of the year 2012, because DateTime datatype can only hold 9999 years.
April 24, 2012 at 1:56 pm
adrian.facio (4/24/2012)
Hey, what's up !It looks like you can only add until 95851 months to a date of the year 2012, because DateTime datatype can only hold 9999 years.
Actually it can only hold 8247 years. They just happen to be numbered from 1753 to 9999.
datetime2 is different of course: it has 9999 years numbered from 1.
Tom
April 24, 2012 at 2:02 pm
ooh you are rigth, i haven't seen it that way.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply