SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a CTE as a Tally Table


Using a CTE as a Tally Table

Author
Message
Adam Aspin
Adam Aspin
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3958 Visits: 1070
Comments posted to this topic are about the item Using a CTE as a Tally Table
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)

Group: General Forum Members
Points: 846207 Visits: 46642
This part of your code causes a full table scan regardless of the TOP 52 because of the ORDER BY in the ROW_NUMBER(). Since you're doing the ORDER BY on the StockCode table, it also does a shedload of unnecessary reads. Also, there is no guarantee that you'll return 52 rows because there's no guarantee that the Data.Stock table will start with or be maintained in such a fashion as to always have at least 52 rows in it because it's a user table rather than a utility or fix system table.

SELECT TOP 52 ROW_NUMBER() OVER (ORDER BY StockCode) AS Num
FROM Data.Stock

This part of your code causes a full table scan because the WHERE clause is non-SARGable as well as there being no indexes on the table.

SELECT SUM(TotalSalePrice) AS SalesForTheWeek
,DatePart(wk, SaleDate) AS WeekNo
FROM Data.Sales
WHERE YEAR(SaleDate) = 2016
GROUP BY DatePart(wk, SaleDate)

Also, you should always use the 2 part naming convention both in the FROM clause (which you've dome) and the SELECT clause of a joined select (which you've not done).

Also, what is a "week"? DATEPART(wk) will only return 2 days for the first week of Jan 2016. The only reason why it returns 7 days for the last week of 2016 is because you get lucky and average of once every 7 years.

Last but not least, your sample data model in the PrestigeCars database is devoid of any and all Primary Keys and indexes.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154059 Visits: 22708
Cannot see any reason for scanning the table to produce the week numbers when it can be done more efficiently without it.
Cool

Here is a far better method with only two constant scans and no sort operator.


USE TEEST;
GO
SET NOCOUNT ON;

;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (52) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2)
SELECT
NM.N AS WEEK_NUM
FROM NUMS NM
ORDER BY NM.N;

BluePeter
BluePeter
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 50
Rather use a recursive cte to create your numbers.

WITH cte AS (
SELECT 1 as Num
UNION ALL
SELECT Num + 1 FROM cte WHERE Num < 52
)
SELECT * FROM cte

This method works well for populating Calendar tables, too.

But if you insist on using an existing table, sys.columns is just about guaranteed to always have enough entries.
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154059 Visits: 22708
BluePeter - Thursday, August 9, 2018 2:20 AM
Rather use a recursive cte to create your numbers.

WITH cte AS (
SELECT 1 as Num
UNION ALL
SELECT Num + 1 FROM cte WHERE Num < 52
)
SELECT * FROM cte

This method works well for populating Calendar tables, too.

But if you insist on using an existing table, sys.columns is just about guaranteed to always have enough entries.

Be careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
Cool

Joe Kelly
Joe Kelly
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 100
A small point, referred to above, there are 53 weeks in a financial year, to catch the few days at each end. Thanks for the article though. Good topic and I'm sure the comments above are meant to help, not criticise.





Tks,

JK

Gerhard Pisch
Gerhard Pisch
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 146
Thank you for the article!
In the discussion everything important is mentioned.But the most important: Without this article no discussion would be happen. Therefore Thank you once more!

Gerhard Pisch
Advanced BI Developer
Austria
BluePeter
BluePeter
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 50
Eirikur Eiriksson - Thursday, August 9, 2018 2:47 AM

Be careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
Cool

Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
But no difference for 52 (or 53)
I'll keep yours in mind, never seen it before.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)

Group: General Forum Members
Points: 846207 Visits: 46642

BluePeter - Thursday, August 9, 2018 5:07 AM
Eirikur Eiriksson - Thursday, August 9, 2018 2:47 AM

Be careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
Cool

Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
But no difference for 52 (or 53)
I'll keep yours in mind, never seen it before.

The reason you think there is no difference is because of what you're measuring and the way your measuring it. There's another thing to consider, as well...

If you practice the wrong way just because of low row counts, you'll never get good at the right way. You also don't have to actually build the cCTE method (Cascading CTEs rather than Recursive CTEs) each and every time you want a sequence of numbers. You can easily build an iTVF (Inline Table Valued Function) that will do that for you and, unlike scalar functions and mTVFs (Mult-statment Table Valued Functions), provides no additional hit if you refer to it within other iTVFs.


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Scott Coleman
Scott Coleman
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23317 Visits: 2021
53 is probably sufficient, but if a leap year starts on Saturday then Sunday Dec 31 will be in week 54. Hopefully they don't mind missing Sunday sales on New Year's Eve once every 28 years. This happens in 1916, 1944, 1972, 2000, 2028.

You could merge that day into week 53:

SELECT   SUM(TotalSalePrice) AS SalesForTheWeek
,CASE w.WeekNo WHEN 54 THEN 53 ELSE w.WeekNo END AS WeekNo
FROM Data.Sales
CROSS APPLY ( SELECT WeekNo = DatePart(wk, SaleDate) ) w
WHERE YEAR(SaleDate) = 2016
GROUP BY CASE w.WeekNo WHEN 54 THEN 53 ELSE w.WeekNo END




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search