|
|
|
Old Hand
 
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 1:59 AM
Points: 358,
Visits: 809
|
|
knechod (3/13/2008)
Is it strictly necessary to use the OVER/Partition clause in the CTE? Isn't it solely to restart the row numbering? Or what am I missing?
Hi,
Hmmm.... You're not wrong! You could remove the partition clause, but you'd have to put ItemId in the OrderBy. Otherwise there will not be an uninterrupted (contiguous) sequence for a specific Item.
so if you prefer
OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
could become
OVER (ORDER BY ph.ItemId, PriceStartDate) AS rownum
I just find conceptually it's easier to understand when the numbering restarts. But it would be interesting to see if the performance improved with your method.
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 1:59 AM
Points: 358,
Visits: 809
|
|
Just a quick note to say thanks to everyone for all their feedback. My previous articles were about xml and xsl and while apparently appreciated, they didn't generate any real discussion.
So I'm delighted to hear what you made of this one.
Thanks again,
David McKinney.
p.s. I think there may be a question mark over the performance for very large datasets, so it's probably not suitable for every scenario, but should work fine for most of us.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 15, 2010 10:58 PM
Points: 190,
Visits: 94
|
|
There are a number of ways to "fake" the rownumber function in sql 2000 -- here is one I particularly like:
http://support.microsoft.com/kb/q186133/
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 20,164,
Visits: 13,699
|
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 08, 2010 5:02 PM
Points: 125,
Visits: 135
|
|
David McKinney (3/14/2008)
knechod (3/13/2008)
Is it strictly necessary to use the OVER/Partition clause in the CTE? Isn't it solely to restart the row numbering? Or what am I missing?Hi, Hmmm.... You're not wrong! You could remove the partition clause, but you'd have to put ItemId in the OrderBy. Otherwise there will not be an uninterrupted (contiguous) sequence for a specific Item. so if you prefer OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum could become OVER (ORDER BY ph.ItemId, PriceStartDate) AS rownum I just find conceptually it's easier to understand when the numbering restarts. But it would be interesting to see if the performance improved with your method.
Actually, I'm getting MUCH too much credit on this. After taking a look at the BOL (heartily recommended before posting!), I see that ROW_NUMBER requires the OVER clause. I apologize for letting my 'Oracle-ness' spill into this discussion! Now, I understand why Partition may or may not be necessary!
--
Please upgrade to .sig 2.0
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 15, 2010 10:58 PM
Points: 190,
Visits: 94
|
|
>>
Before you use any of those that have a self join or correlated subquery that use the ">=" or "<=" or any other inequality method to generate row numbers, you should really read the following article...
<<
Thanks Jeff. I do realize that this is a form of RBAR.
FWIW I have not had to use these because I haven't ever done it in SQL 2000 myself. (I didn't use SQL 2000 very much at all, actually.)
However, when one post examples using Oracle or SQL 2005 you often get a question about "how to do this in SQL 2000". I usually post that link because I find it clear and easy to follow.
If you would like to suggest an alternate link to provide, with an appropriate method that you find more performant or better suited as a recommendation for these folks, please advise.
>L<
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 1:16 AM
Points: 357,
Visits: 1,966
|
|
I hope to show in this article that the CTEs usefulness is not purely cosmetic.
You are right david. It would be immensely useful in more complicated situations. I would have to do as follows without the use of CTE in the present example.
CREATE VIEW [dbo].[PriceCompare] AS
SELECT
currow.Item,
prevrow.Price AS OldPrice,
currow.Price AS RangePrice,
currow.PriceStartDate AS StartDate,
nextrow.PriceStartDate AS EndDate
FROM
(SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM Items i INNER JOIN PriceHistory ph
ON i.ItemId = ph.ItemId) currow
LEFT JOIN
(SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM Items i INNER JOIN PriceHistory ph
ON i.ItemId = ph.ItemId) nextrow
ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId
LEFT JOIN
(SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM Items i INNER JOIN PriceHistory ph
ON i.ItemId = ph.ItemId) prevrow
ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId
Too clumsy and reiterative as you see. I am looking forward to your input for 'using CTEs with recursive queries'
Best regards,
Maz
-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 20,164,
Visits: 13,699
|
|
Lisa Slater Nicholls (3/16/2008)
>>
Before you use any of those that have a self join or correlated subquery that use the ">=" or "<=" or any other inequality method to generate row numbers, you should really read the following article...
<<
Thanks Jeff. I do realize that this is a form of RBAR.
FWIW I have not had to use these because I haven't ever done it in SQL 2000 myself. (I didn't use SQL 2000 very much at all, actually.)
However, when one post examples using Oracle or SQL 2005 you often get a question about "how to do this in SQL 2000". I usually post that link because I find it clear and easy to follow.
If you would like to suggest an alternate link to provide, with an appropriate method that you find more performant or better suited as a recommendation for these folks, please advise.
>L<
Thank you very much for the feedback, Lisa. I usually do the same because SQL Server 2000 is still so very prevalent...
As for a suggestion on how to do a "rownumber" in SQL Server 2000, as odd as it seems, a SELECT INTO on a temp table with an identity column is so very fast that it's nearly as fast as SQL Server 2005's ROW_NUMBER() over. Yes, the ROW_NUMBER function is still faster, but the SELECT INTO method is, I believe, the fastest method you can achieve in SQL Server 2000 even when a million rows are involved.
And, I didn't mean to criticise... only to advise. I apologize if the former seemed to be the case.
--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."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 15, 2010 10:58 PM
Points: 190,
Visits: 94
|
|
>>
SELECT INTO on a temp table with an identity column is so very fast that it's nearly as fast as SQL Server 2005's ROW_NUMBER() over
<<
That's extremely cool, Jeff -- not ever having tested I would not have thought it would be fast, but from now on I will remember to suggest that, thank you.
I wonder if that is how it's implemented, under the covers, in SQL Server 2005.
>L<
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 12:58 PM
Points: 746,
Visits: 1,213
|
|
Lisa Slater Nicholls (3/16/2008)
>>
However, when one post examples using Oracle or SQL 2005 you often get a question about "how to do this in SQL 2000". I usually post that link because I find it clear and easy to follow.
If you would like to suggest an alternate link to provide, with an appropriate method that you find more performant or better suited as a recommendation for these folks, please advise.
>L<
Lisa,
The code I posted here a few days ago is SQL Server 2000 compatible. It is the same as Jeff's SELECT INTO, except that I have explicity defined the table structure. It returns the same data as the original CTE example this thread is based on.
http://www.sqlservercentral.com/Forums/FindPost468899.aspx
The field names were changed to work with a database that I have handy.
I've used this temp table-identity column many times in the past for various reasons, and haven't found it to cause any performance issues.
Tom Garth
Vertical Solutions
"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
|
|
|
|
