October 8, 2012 at 1:50 am
ChrisM@Work (10/8/2012)
dwain.c (10/8/2012)
ChrisM@Work (10/8/2012)
Hi Dwain, I came up with this last night but ran out of time to post;
;WITH WholeRange AS (SELECT datecol = DATEADD(day,n,d.Startdate)
FROM (SELECT Startdate = MIN(datecol), daysSpan = 1+DATEDIFF(day,MIN(datecol), MAX(datecol)) FROM #SAMPLETABLE) d
CROSS APPLY (SELECT TOP(daysSpan) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns a, sys.columns b) x
)
SELECT * --left like this for testing
FROM WholeRange w
CROSS APPLY (
SELECT TOP 1 s.*
FROM #SAMPLETABLE s
WHERE s.datecol <= w.datecol
ORDER BY s.datecol DESC
) x
You'll notice it's exactly the same as Nagaram's query - except for the number generator.
I can't find anything wrong with it?
My mistake! I didn't take into account that row ordering of the OP's query was different. Correct number of rows is returned by his, yours and my Quirky Update.
As usual, yours seems to be the fastest. :angry:
Nagaram's is the fastest so far - it's a good 20% faster than mine. I'm having a look to find out why - probably the tally-table generation. Yours does an update (and very quickly too) so it's a bit "oranges and apples".
After I removed the ORDER BY yours and Nagaram's tied. Interesting that you think his should be faster.
If I'd have been able to get my Quirky Merge to work, I'd have won. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 8, 2012 at 2:31 am
dwain.c (10/8/2012)
<<snip>>Nagaram's is the fastest so far - it's a good 20% faster than mine. I'm having a look to find out why - probably the tally-table generation. Yours does an update (and very quickly too) so it's a bit "oranges and apples".
After I removed the ORDER BY yours and Nagaram's tied. Interesting that you think his should be faster.
If I'd have been able to get my Quirky Merge to work, I'd have won. 😀
It might be this lappy 😉
Had some fun playing with the virtual tally tables. Nagaram's was definitely faster, but not by much;
PRINT 'Nagaram (reformatted) =================================================='
SET STATISTICS IO, TIME ON
;WITH DigitsCTE AS (SELECT digit FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS D(digit))
, AllDatesCTE AS (
SELECT date = DATEADD(DAY, N.number - 1, T.min_date)
FROM (SELECT min_date = MIN(T.DATECOL), max_date = MAX(T.DATECOL) FROM #SAMPLETABLE AS T) AS T
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY, T.min_date, T.max_date) + 1)
number = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM DigitsCTE AS D0, DigitsCTE AS D1, DigitsCTE AS D2,
DigitsCTE AS D3, DigitsCTE AS D4, DigitsCTE AS D5) AS N
) SELECT * FROM AllDatesCTE
SET STATISTICS IO, TIME OFF
PRINT ''
PRINT 'ChrisM original ========================================================'
SET STATISTICS IO, TIME ON
;WITH WholeRange AS (
SELECT datecol = DATEADD(day,n,d.Startdate)
FROM (SELECT Startdate = MIN(datecol), daysSpan = 1+DATEDIFF(day,MIN(datecol), MAX(datecol)) FROM #SAMPLETABLE) d
CROSS APPLY (SELECT TOP(daysSpan) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns a, sys.columns b) x
) SELECT * FROM WholeRange
SET STATISTICS IO, TIME OFF
PRINT ''
PRINT 'ChrisM new ============================================================='
SET STATISTICS IO, TIME ON
;WITH WholeRange AS (
SELECT x.datecol
FROM (SELECT Startdate = MIN(datecol), Enddate = MAX(datecol) FROM #SAMPLETABLE) d
CROSS APPLY (
SELECT TOP(DATEDIFF(day,Startdate, Enddate) + 1)
datecol = DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,d.Startdate)
FROM
(VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS g(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS h(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS i(digit)
) x
) SELECT * FROM WholeRange
SET STATISTICS IO, TIME OFF
Edit: fixed quote
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 2:35 am
dwain.c (10/7/2012)
I find it extremely annoying that this does not work:
DECLARE @Weight FLOAT = 0
,@STDate DATETIME
,@EDate DATETIME
SELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE
;WITH Tally AS (
SELECT TOP (1+DATEDIFF(day, @STDate, @EDate)) n=number-1
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND number BETWEEN 1 AND 100)
MERGE #SAMPLETABLE t
USING Tally s
ON t.DATECOL = DATEADD(day, n, @STDate)
WHEN MATCHED THEN
UPDATE SET @Weight = WEIGHTS
WHEN NOT MATCHED THEN
INSERT (DATECOL, WEIGHTS)
VALUES (DATEADD(day, n, @STDate), @Weight);
SELECT *
FROM #SAMPLETABLE
ORDER BY DATECOL
DROP TABLE #SAMPLETABLE
When BOL (http://technet.microsoft.com/en-us/library/bb510625.aspx) says that you should be able to SET assign to a local variable.
Dwain - I tried this too, a few months ago. It's bl@@dy irritating that it doesn't appear to work when BOL suggests it should.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 3:59 am
I like your:
VALUES ($)
Wonder where you got that from... 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 8, 2012 at 4:16 am
ChrisM@Work (10/8/2012)
dwain.c (10/8/2012)
<<snip>>Nagaram's is the fastest so far - it's a good 20% faster than mine. I'm having a look to find out why - probably the tally-table generation. Yours does an update (and very quickly too) so it's a bit "oranges and apples".
After I removed the ORDER BY yours and Nagaram's tied. Interesting that you think his should be faster.
If I'd have been able to get my Quirky Merge to work, I'd have won. 😀
It might be this lappy 😉
Had some fun playing with the virtual tally tables. Nagaram's was definitely faster, but not by much;
PRINT 'Nagaram (reformatted) =================================================='SET STATISTICS IO, TIME ON
;WITH DigitsCTE AS (SELECT digit FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS D(digit))
, AllDatesCTE AS (
SELECT date = DATEADD(DAY, N.number - 1, T.min_date)
FROM (SELECT min_date = MIN(T.DATECOL), max_date = MAX(T.DATECOL) FROM #SAMPLETABLE AS T) AS T
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY, T.min_date, T.max_date) + 1)
number = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM DigitsCTE AS D0, DigitsCTE AS D1, DigitsCTE AS D2,
DigitsCTE AS D3, DigitsCTE AS D4, DigitsCTE AS D5) AS N
) SELECT * FROM AllDatesCTE
SET STATISTICS IO, TIME OFF
PRINT ''
PRINT 'ChrisM original ========================================================'
SET STATISTICS IO, TIME ON
;WITH WholeRange AS (
SELECT datecol = DATEADD(day,n,d.Startdate)
FROM (SELECT Startdate = MIN(datecol), daysSpan = 1+DATEDIFF(day,MIN(datecol), MAX(datecol)) FROM #SAMPLETABLE) d
CROSS APPLY (SELECT TOP(daysSpan) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns a, sys.columns b) x
) SELECT * FROM WholeRange
SET STATISTICS IO, TIME OFF
PRINT ''
PRINT 'ChrisM new ============================================================='
SET STATISTICS IO, TIME ON
;WITH WholeRange AS (
SELECT x.datecol
FROM (SELECT Startdate = MIN(datecol), Enddate = MAX(datecol) FROM #SAMPLETABLE) d
CROSS APPLY (
SELECT TOP(DATEDIFF(day,Startdate, Enddate) + 1)
datecol = DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,d.Startdate)
FROM
(VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS g(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS h(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS i(digit)
) x
) SELECT * FROM WholeRange
SET STATISTICS IO, TIME OFF
Edit: fixed quote
Did you forget something? Like possibly outputting the propagated weights?
I thought output like this was what we were looking for?
DECLARE @STDate DATETIME, @EDate DATETIME
SELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE
;WITH Calendar (n) AS (
SELECT TOP (1+DATEDIFF(day, @STDate, @EDate))
DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STDate)
FROM sys.all_columns a, sys.all_columns b)
SELECT DATECOL=n
,WEIGHTS=CASE WHEN WEIGHTS IS NOT NULL THEN WEIGHTS
ELSE (
SELECT TOP 1 WEIGHTS
FROM #SAMPLETABLE c
WHERE c.DATECOL < n
ORDER BY c.DATECOL DESC
) END
FROM Calendar a
LEFT OUTER JOIN #SAMPLETABLE b ON n = DATECOL
Not that I think my subquery version will be faster or anything. Just sayin'.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 8, 2012 at 4:21 am
dwain.c (10/8/2012)
I like your:
VALUES ($)
Wonder where you got that from... 😛
I know you like them - so I put in lots.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 4:26 am
ChrisM@Work (10/8/2012)
dwain.c (10/8/2012)
I like your:
VALUES ($)
Wonder where you got that from... 😛
I know you like them - so I put in lots.
I like Euros better, just don't have that character on my keyboard.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 8, 2012 at 4:58 am
dwain.c (10/8/2012)
ChrisM@Work (10/8/2012)
dwain.c (10/8/2012)
I like your:
VALUES ($)
Wonder where you got that from... 😛
I know you like them - so I put in lots.
I like Euros better, just don't have that character on my keyboard.
We call 'em Yoyos - next year you will probably find out why 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 5:05 am
dwain.c (10/8/2012)
ChrisM@Work (10/8/2012)
dwain.c (10/8/2012)
<<snip>>Nagaram's is the fastest so far - it's a good 20% faster than mine. I'm having a look to find out why - probably the tally-table generation. Yours does an update (and very quickly too) so it's a bit "oranges and apples".
After I removed the ORDER BY yours and Nagaram's tied. Interesting that you think his should be faster.
If I'd have been able to get my Quirky Merge to work, I'd have won. 😀
It might be this lappy 😉
Had some fun playing with the virtual tally tables. Nagaram's was definitely faster, but not by much;
PRINT 'Nagaram (reformatted) =================================================='SET STATISTICS IO, TIME ON
;WITH DigitsCTE AS (SELECT digit FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS D(digit))
, AllDatesCTE AS (
SELECT date = DATEADD(DAY, N.number - 1, T.min_date)
FROM (SELECT min_date = MIN(T.DATECOL), max_date = MAX(T.DATECOL) FROM #SAMPLETABLE AS T) AS T
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY, T.min_date, T.max_date) + 1)
number = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM DigitsCTE AS D0, DigitsCTE AS D1, DigitsCTE AS D2,
DigitsCTE AS D3, DigitsCTE AS D4, DigitsCTE AS D5) AS N
) SELECT * FROM AllDatesCTE
SET STATISTICS IO, TIME OFF
PRINT ''
PRINT 'ChrisM original ========================================================'
SET STATISTICS IO, TIME ON
;WITH WholeRange AS (
SELECT datecol = DATEADD(day,n,d.Startdate)
FROM (SELECT Startdate = MIN(datecol), daysSpan = 1+DATEDIFF(day,MIN(datecol), MAX(datecol)) FROM #SAMPLETABLE) d
CROSS APPLY (SELECT TOP(daysSpan) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns a, sys.columns b) x
) SELECT * FROM WholeRange
SET STATISTICS IO, TIME OFF
PRINT ''
PRINT 'ChrisM new ============================================================='
SET STATISTICS IO, TIME ON
;WITH WholeRange AS (
SELECT x.datecol
FROM (SELECT Startdate = MIN(datecol), Enddate = MAX(datecol) FROM #SAMPLETABLE) d
CROSS APPLY (
SELECT TOP(DATEDIFF(day,Startdate, Enddate) + 1)
datecol = DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,d.Startdate)
FROM
(VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS g(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS h(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS i(digit)
) x
) SELECT * FROM WholeRange
SET STATISTICS IO, TIME OFF
Edit: fixed quote
Did you forget something? Like possibly outputting the propagated weights?
I thought output like this was what we were looking for?
DECLARE @STDate DATETIME, @EDate DATETIME
SELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE
;WITH Calendar (n) AS (
SELECT TOP (1+DATEDIFF(day, @STDate, @EDate))
DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STDate)
FROM sys.all_columns a, sys.all_columns b)
SELECT DATECOL=n
,WEIGHTS=CASE WHEN WEIGHTS IS NOT NULL THEN WEIGHTS
ELSE (
SELECT TOP 1 WEIGHTS
FROM #SAMPLETABLE c
WHERE c.DATECOL < n
ORDER BY c.DATECOL DESC
) END
FROM Calendar a
LEFT OUTER JOIN #SAMPLETABLE b ON n = DATECOL
Not that I think my subquery version will be faster or anything. Just sayin'.
Tried it already - it's the same performance as the rest.
Here's the bit you moaned about four posts back:
SELECT x.datecol, y.WEIGHTS
FROM (SELECT Startdate = MIN(datecol), Enddate = MAX(datecol) FROM #SAMPLETABLE) d
CROSS APPLY (
SELECT TOP(DATEDIFF(day,d.Startdate, d.Enddate) + 1)
datecol = DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,d.Startdate)
FROM
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS e(digit),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS f(digit),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS g(digit),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS h(digit),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS i(digit)
) x
CROSS APPLY (
SELECT TOP 1 si.WEIGHTS
FROM #SAMPLETABLE si
WHERE si.datecol <= x.datecol
ORDER BY si.datecol DESC
) y
ORDER BY x.datecol
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 5:25 am
I'm very thank ful to all .
Its great pleasure to know all the valueable suggestions and solutions.
October 8, 2012 at 6:07 am
Chris - I'm gonna keep moanin' until I win one of these performance races against you.
So I'll submit this (stealing your Tally table):
;WITH Tally (n) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM (VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit)
) ,
MyData AS (
SELECT DateCol, Weights
,rn=ROW_NUMBER() OVER (ORDER BY DateCol)
FROM #SAMPLETABLE
)
SELECT DateCol=CASE WHEN c.DateCol IS NULL THEN a.DateCol ELSE c.DateCol END
, a.Weights
FROM MyData a
LEFT OUTER JOIN MyData b ON a.rn = b.rn - 1
OUTER APPLY (
SELECT DATEADD(day, n, a.DateCol)
FROM Tally
WHERE n BETWEEN 0 AND DATEDIFF(day, a.DateCol, b.DateCol)-1) c(DateCol)
Looks tight on my lappie!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 8, 2012 at 7:33 am
dwain.c (10/8/2012)
Chris - I'm gonna keep moanin' until I win one of these performance races against you.So I'll submit this (stealing your Tally table):
;WITH Tally (n) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM (VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit)
) ,
MyData AS (
SELECT DateCol, Weights
,rn=ROW_NUMBER() OVER (ORDER BY DateCol)
FROM #SAMPLETABLE
)
SELECT DateCol=CASE WHEN c.DateCol IS NULL THEN a.DateCol ELSE c.DateCol END
, a.Weights
FROM MyData a
LEFT OUTER JOIN MyData b ON a.rn = b.rn - 1
OUTER APPLY (
SELECT DATEADD(day, n, a.DateCol)
FROM Tally
WHERE n BETWEEN 0 AND DATEDIFF(day, a.DateCol, b.DateCol)-1) c(DateCol)
Looks tight on my lappie!
It's nice, Dwain, but using a filter on the tally table isn't as quick as using TOP;
;WITH SequencedData AS (SELECT DateCol, Weights, rn = ROW_NUMBER() OVER (ORDER BY DateCol) FROM #SAMPLETABLE)
SELECT DateCol = ISNULL(t.datecol,a.DateCol), a.Weights
FROM SequencedData a
LEFT OUTER JOIN SequencedData b ON a.rn = b.rn-1
OUTER APPLY (
SELECT TOP(ISNULL(DATEDIFF(day, a.DateCol,b.DateCol),0))
DateCol = DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,a.DateCol)
FROM (VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit)) t
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 11:19 am
dwain.c (10/7/2012)[hrJeff - I don't think this solution works with your test harness.
I did say that I needed to make a change to get it to work and described the change.
--Jeff Moden
October 8, 2012 at 11:31 am
Jeff Moden (10/8/2012)
dwain.c (10/7/2012)[hrJeff - I don't think this solution works with your test harness.
I did say that I needed to make a change to get it to work and described the change.
dwain.c (10/8/2012)
...
You'll notice it's exactly the same as Nagaram's query - except for the number generator.
I can't find anything wrong with it?
My mistake! I didn't take into account that row ordering of the OP's query was different. Correct number of rows is returned by his, yours and my Quirky Update...
Jeff - Dwain was a bit cranky when he wrote that, his comb-over took off in the wind on the way to work 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 5:21 pm
Jeff Moden (10/8/2012)
dwain.c (10/7/2012)[hrJeff - I don't think this solution works with your test harness.
I did say that I needed to make a change to get it to work and described the change.
Actually I retracted tat statement later on.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply