March 13, 2012 at 9:41 am
andrew.diniz (3/13/2012)
Apart from rCTE's scaling linearly, this statement is not correct. Read Jeff's article. A table, however large or small, will always perform many times faster than a rCTE.
That's simply not true. In 2005 the cost of a rCTE to generate the list of 12 chronologically sorted months is a fraction of the cost of selecting distinct month names across a table.
And this smokes the rCTE
CREATE VIEW vw_months
AS
WITH months AS (
SELECT monthindex = 1, month_name = 'January'
UNION ALL
SELECT monthindex = 2, month_name = 'February'
UNION ALL
SELECT monthindex = 3, month_name = 'March'
UNION ALL
SELECT monthindex = 4, month_name = 'April'
UNION ALL
SELECT monthindex = 5, month_name = 'May'
UNION ALL
SELECT monthindex = 6, month_name = 'June'
UNION ALL
SELECT monthindex = 7, month_name = 'July'
UNION ALL
SELECT monthindex = 8, month_name = 'August'
UNION ALL
SELECT monthindex = 9, month_name = 'September'
UNION ALL
SELECT monthindex = 10, month_name = 'October'
UNION ALL
SELECT monthindex = 11, month_name = 'November'
UNION ALL
SELECT monthindex = 12, month_name = 'December'
)
SELECT
monthindex,
month_name
FROM
months
Then just use
SELECT
*
FROM vw_months
ORDER BY monthindex
If the discussion is about simplicity, I have never seen Recursion coupled with that. The above is very simple... and reusable.
March 13, 2012 at 9:54 am
andrew.diniz (3/13/2012)
Apart from rCTE's scaling linearly, this statement is not correct. Read Jeff's article. A table, however large or small, will always perform many times faster than a rCTE.
That's simply not true. In 2005 the cost of a rCTE to generate the list of 12 chronologically sorted months is a fraction of the cost of selecting distinct month names across a table.
so I tapped my calendar dimension which has a record for each day of the year in it.
CREATE TABLE [dbo].[dim_calendar](
[Calendar_Key] [int] IDENTITY(1,1) NOT NULL,
[Calendar_Date] [date] NOT NULL,
[US_Holiday_Indicator] [varchar](15) NULL,
[CA_Holiday_Indicator] [varchar](15) NULL,
[US_Working_Days_In_Month] [int] NOT NULL,
[CA_Working_Days_In_Month] [int] NOT NULL,
[US_Working_Hours_In_Day] [int] NOT NULL,
[CA_Working_Hours_In_Day] [int] NOT NULL,
[Week_Ending_Date] AS (dateadd(day,(7)-datepart(weekday,[Calendar_Date]),[Calendar_date])),
[Year_Numeric] AS (datepart(year,[calendar_date])),
[Year_Text] AS (datename(year,[Calendar_Date])),
[Quarter_Numeric] AS (datepart(quarter,[Calendar_Date])),
[Quarter_Smart_key] AS (datepart(year,[Calendar_Date])*(100)+datepart(quarter,[Calendar_date])),
[Quarter_Text_QQYYYY] AS ((('Q'+datename(quarter,[Calendar_Date]))+' ')+datename(year,[Calendar_Date])),
[Quarter_Text_YYYYQQ] AS ((datename(year,[Calendar_Date])+' Q')+datename(quarter,[Calendar_Date])),
[Month_Numeric] AS (datepart(month,[Calendar_Date])),
[Month_Smart_Key] AS (datepart(year,[Calendar_Date])*(100)+datepart(month,[Calendar_Date])),
[Month_Text] AS ((datename(month,[Calendar_Date])+' ')+datename(year,[CAlendar_Date])),
[Day_Numeric] AS (datepart(day,[Calendar_Date])),
[Day_Smart_Key] AS (((datepart(year,[Calendar_Date])*(1000000)+datepart(quarter,[Calendar_Date])*(10000))+datepart(month,[Calendar_Date])*(100))+datepart(day,[Calendar_Date])),
[Day_In_Week] AS (datepart(weekday,[Calendar_Date])),
[Day_Text] AS ((((datename(month,[Calendar_Date])+' ')+datename(day,[Calendar_Date]))+', ')+datename(year,[Calendar_Date])),
[Day_Name] AS (datename(weekday,[Calendar_Date])),
[Day_Type] AS (case when datename(weekday,[Calendar_Date])='Sunday' OR datename(weekday,[Calendar_Date])='Saturday' then 'Weekend' else 'Weekday' end),
[Thirty_Day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(30) else datediff(day,[Calendar_Date],getdate())/(30) end),
[Thirty_Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(30)),(0))+' 30 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(30) when (0) then 'Current 30 Day Period' when (1) then 'Prior 30 Day Period' when (2) then 'Tertiary 30 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(30),(0))+' 30 Day Periods Ago' end end),
[Ninety_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(90) else datediff(day,[Calendar_Date],getdate())/(90) end),
[Ninety_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(90)),(0))+' 90 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(90) when (0) then 'Current 90 Day Period' when (1) then 'Prior 90 Day Period' when (2) then 'Tertiary 90 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(90),(0))+' 90 Day Periods Ago' end end),
[Threehundredsixtyfive_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(365) else datediff(day,[Calendar_Date],getdate())/(365) end),
[Threehundredsixtyfive_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(365)),(0))+' 365 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(365) when (0) then 'Current 365 Day Period' when (1) then 'Prior 365 Day Period' when (2) then 'Tertiary 365 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(365),(0))+' 365 Day Periods Ago' end end),
[Six_Month_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(month,[Calendar_Date],getdate())/(6) else datediff(month,[Calendar_Date],getdate())/(6) end),
[Six_Month_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(month,[Calendar_Date],getdate())/(6)),(0))+' 6 Month Period(s) in the Future' else case datediff(month,[Calendar_Date],getdate())/(6) when (0) then 'Current 6 Month Period' when (1) then 'Prior 6 Month Period' when (2) then 'Tertiary 6 Month Period' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate())/(6),(0))+' 6 Month Periods Ago' end end),
[Quarters_Periods] AS (datediff(quarter,[Calendar_Date],getdate())),
[Quarters_Periods_Text] AS (case when datediff(quarter,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(quarter,[Calendar_Date],getdate())),(0))+' Quarter(s) in the future' else case datediff(quarter,[Calendar_Date],getdate()) when (0) then 'Current Quarter' when (1) then 'Prior Quarter' when (2) then 'Tertiary Quarter' else CONVERT([varchar],datediff(quarter,[Calendar_Date],getdate()),(0))+' Quarters Ago' end end),
[Months_Periods] AS (datediff(month,[Calendar_Date],getdate())),
[Months_Periods_Text] AS (case when datediff(month,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(month,[Calendar_Date],getdate())),(0))+' Month(s) in the Future' else case datediff(month,[Calendar_Date],getdate()) when (0) then 'Current Month' when (1) then 'Prior Month' when (2) then 'Tertiary Month' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate()),(0))+' Months Ago' end end),
[Day_Periods] AS (datediff(day,[Calendar_Date],getdate())),
[Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then case datediff(day,[Calendar_Date],getdate()) when (-1) then 'Tomorrow' else CONVERT([varchar],abs(datediff(day,[Calendar_Date],getdate())),(0))+' Days in the future' end when datediff(day,[Calendar_Date],getdate())=(0) then 'Today' when datediff(day,[Calendar_Date],getdate())=(1) then 'Yesterday' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate()),(0))+' Days Ago' end),
[Week_Periods] AS (datediff(week,[Calendar_Date],getdate())),
[Week_Periods_Text] AS (case when datediff(week,[Calendar_Date],getdate())<(0) then case datediff(week,[Calendar_Date],getdate()) when (-1) then 'Next Week' else CONVERT([varchar],abs(datediff(week,[Calendar_Date],getdate())),(0))+' Weeks in the future' end when datediff(week,[Calendar_Date],getdate())=(0) then 'This Week' when datediff(week,[Calendar_Date],getdate())=(1) then 'Last Week' else CONVERT([varchar],datediff(week,[Calendar_Date],getdate()),(0))+' Weeks Ago' end),
[Days_In_Month] AS (datediff(day,dateadd(month,datediff(month,(0),[Calendar_Date]),(0)),dateadd(month,(1)+datediff(month,(0),[Calendar_Date]),(0)))),
[day_number_in_month] AS (datepart(day,[calendar_date])),
[day_number_in_30_day_periods] AS ((30)-datediff(day,[calendar_date],getdate())%(30)),
[day_number_in_90_day_periods] AS ((90)-datediff(day,[calendar_date],getdate())%(90)),
[day_number_in_365_day_periods] AS ((365)-datediff(day,[calendar_date],getdate())%(365)),
[day_number_in_quarter] AS (case when [calendar_date]<=CONVERT([date],sysdatetime(),0) then (1) else (-1) end+datediff(day,CONVERT([date],((CONVERT([varchar],datepart(year,[calendar_date]),0)+'-')+CONVERT([varchar],datepart(quarter,[calendar_date])*(3)-(2),0))+'-01',0),[calendar_date])),
[Quarter_text] AS ('Q'+datename(quarter,[Calendar_Date])),
[Month_name] AS (datename(month,[Calendar_Date])),
[Day_number_text] AS (datename(day,[calendar_date])),
[Year_period_numeric] AS (datediff(year,[Calendar_Date],getdate())),
[Year_period_text] AS (case datediff(year,[Calendar_Date],getdate()) when (0) then 'Current Year' when (1) then 'Last Year' when (2) then 'Prior Year' when (3) then 'Tertiary Year' when (-1) then 'Next Year' else case when datediff(year,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(year,[Calendar_Date],getdate())),0)+' Years From Now' else CONVERT([varchar],abs(datediff(year,[Calendar_Date],getdate())),0)+' Years Ago' end end),
[month_end] AS (CONVERT([date],dateadd(day,(-1),dateadd(month,(1)+datediff(month,(0),[calendar_date]),(0))),0)),
CONSTRAINT [PK__dim_cale__3C52D19446486B8E] PRIMARY KEY CLUSTERED
(
[Calendar_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here's all I wrote to get the data. You can populate the above table however you want. Some of the columns may be meaningless to you however as I said this is just my calendar dimension table in my DW.
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
DISTINCT
Month_Smart_Key,
Month_name
FROM dbo.dim_calendar
WHERE year_numeric = 2012
ORDER BY Month_Smart_Key
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
My results
(12 row(s) affected)
Table 'dim_calendar'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Here's the recursive CTE solution.
SET STATISTICS IO ON
SET STATISTICS TIME ON
;WITH CTEMonth
AS
(
SELECT 1 AS MonNum
UNION ALL
SELECT MonNum + 1 -- add month number to 1 recursively
FROM CTEMonth
WHERE MonNum < 12 -- just to restrict the monthnumber upto 12
)
SELECT
MonNum,
DATENAME(MONTH,DATEADD(MONTH,MonNum,0)- 1)[MonthName] -- function to list the monthname.
FROM CTEMonth
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
And the results
(12 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
The elapsed time differential is virtually negligable, but the Scan and Logical reads counts are not. And the Scan and Logical reads are consistent, while cpu time is dependant on many factors.
March 13, 2012 at 9:54 am
but you must have a tally table in your database. and you may not need a calendar table.
March 13, 2012 at 9:55 am
cool
March 13, 2012 at 9:55 am
alfredoapereira (3/13/2012)
but you must have a tally table in your database. and you may not need a calendar table.
Not true... the Tally CTE outperforms the Tally Table. But even it isn't recursive.
March 13, 2012 at 9:59 am
mtassin (3/13/2012)
andrew.diniz (3/13/2012)
Apart from rCTE's scaling linearly, this statement is not correct. Read Jeff's article. A table, however large or small, will always perform many times faster than a rCTE.
That's simply not true. In 2005 the cost of a rCTE to generate the list of 12 chronologically sorted months is a fraction of the cost of selecting distinct month names across a table.
And this smokes the rCTE
CREATE VIEW vw_months
AS
WITH months AS (
SELECT monthindex = 1, month_name = 'January'
UNION ALL
SELECT monthindex = 2, month_name = 'February'
UNION ALL
SELECT monthindex = 3, month_name = 'March'
UNION ALL
SELECT monthindex = 4, month_name = 'April'
UNION ALL
SELECT monthindex = 5, month_name = 'May'
UNION ALL
SELECT monthindex = 6, month_name = 'June'
UNION ALL
SELECT monthindex = 7, month_name = 'July'
UNION ALL
SELECT monthindex = 8, month_name = 'August'
UNION ALL
SELECT monthindex = 9, month_name = 'September'
UNION ALL
SELECT monthindex = 10, month_name = 'October'
UNION ALL
SELECT monthindex = 11, month_name = 'November'
UNION ALL
SELECT monthindex = 12, month_name = 'December'
)
SELECT
monthindex,
month_name
FROM
months
Then just use
SELECT
*
FROM vw_months
ORDER BY monthindex
If the discussion is about simplicity, I have never seen Recursion coupled with that. The above is very simple... and reusable.
What if you want it in a different language?
March 13, 2012 at 9:59 am
mtassin (3/13/2012)
Koen Verbeeck (3/13/2012)
alfredoapereira (3/13/2012)
Hi you all.Well, I prefer to use a tally table. more clean.
I prefer to use a calendar table, even more clean 😉
I use a Tally table to maintain my calendar table. 🙂
Cool.
March 13, 2012 at 10:00 am
Koen Verbeeck (3/13/2012)
alfredoapereira (3/13/2012)
Hi you all.Well, I prefer to use a tally table. more clean.
I prefer to use a calendar table, even more clean 😉
but you must have a tally table in your database. and you may not need a calendar table.
March 13, 2012 at 10:07 am
Jonathan AC Roberts (3/13/2012)
What if you want it in a different language?
Then use this
WITH Months AS (
SELECT monthindex = 1, month_name = DATENAME(mm,DATEADD(mm,0,0))
UNION ALL
SELECT monthindex = 2, month_name = DATENAME(mm,DATEADD(mm,1,0))
UNION ALL
SELECT monthindex = 3, month_name = DATENAME(mm,DATEADD(mm,2,0))
UNION ALL
SELECT monthindex = 4, month_name = DATENAME(mm,DATEADD(mm,3,0))
UNION ALL
SELECT monthindex = 5, month_name = DATENAME(mm,DATEADD(mm,4,0))
UNION ALL
SELECT monthindex = 6, month_name = DATENAME(mm,DATEADD(mm,5,0))
UNION ALL
SELECT monthindex = 7, month_name = DATENAME(mm,DATEADD(mm,6,0))
UNION ALL
SELECT monthindex = 8, month_name = DATENAME(mm,DATEADD(mm,7,0))
UNION ALL
SELECT monthindex = 9, month_name = DATENAME(mm,DATEADD(mm,8,0))
UNION ALL
SELECT monthindex = 10, month_name = DATENAME(mm,DATEADD(mm,9,0))
UNION ALL
SELECT monthindex = 11, month_name = DATENAME(mm,DATEADD(mm,10,0))
UNION ALL
SELECT monthindex = 12, month_name = DATENAME(mm,DATEADD(mm,11,0))
)
SELECT
*
FROM Months
ORDER BY monthindex
It's still simpler than using recursion in places you shouldn't.
March 13, 2012 at 10:14 am
alfredoapereira (3/13/2012)
Koen Verbeeck (3/13/2012)
alfredoapereira (3/13/2012)
Hi you all.Well, I prefer to use a tally table. more clean.
I prefer to use a calendar table, even more clean 😉
but you must have a tally table in your database. and you may not need a calendar table.
You don't need the tally table in your database... you can use this CTE, which isn't recursive but is incredibly fast.
WITH t1 AS (
SELECT N=1
UNION ALL
SELECT N=1
UNION ALL
SELECT N=1
UNION ALL
SELECT N=1
UNION ALL
SELECT N=1
UNION ALL
SELECT N=1
UNION ALL
SELECT N=1
UNION ALL
SELECT N=1
UNION ALL
SELECT N=1
UNION ALL
SELECT N=1
),
t2 AS (SELECT N=1 FROM t1 a,t1 b),
t3 AS (SELECT N=1 FROM t2 a,t2 b),
cte_tally AS (SELECT N=ROW_NUMBER() OVER (ORDER BY N) FROM t3)
SELECT * FROM cte_tally
You could make it a view, you could just use it. Up to you... if you need a smaller tally table... then you can scale it down and use t2 with 100 rows or multiply t2 by t1 for 1000 rows instead.
March 13, 2012 at 10:17 am
Just to end arguments over performance I tried this:
DECLARE @StartTime datetime
DECLARE @x int
DECLARE @y varchar(20)
DECLARE @MaxIterations int
SET @MaxIterations = 10000
SET @StartTime = GETDATE()
DECLARE @i int
SET @i = 0
WHILE @i < @MaxIterations
BEGIN
;WITH CTEMonth
AS
(
SELECT 1 AS MonNum
UNION ALL
SELECT MonNum + 1 -- add month number to 1 recursively
FROM CTEMonth
WHERE MonNum < 12 -- just to restrict the monthnumber upto 12
)
SELECT
@x = MonNum
,@y = DATENAME(MONTH,DATEADD(MONTH,MonNum,0)- 1) -- function to list the monthname.
FROM CTEMonth
SET @i = @i + 1
END
--END WHILE
PRINT 'Recursive CTE milliseconds ' + CAST(DATEDIFF(ms, @StartTime , GETDATE()) AS varchar(13))
SET @StartTime = GETDATE()
SET @i = 0
WHILE @i < @MaxIterations
BEGIN
;WITH Months AS
(
SELECT monthindex = 1, month_name = DATENAME(mm,DATEADD(mm,0,0)) UNION ALL
SELECT monthindex = 2, month_name = DATENAME(mm,DATEADD(mm,1,0)) UNION ALL
SELECT monthindex = 3, month_name = DATENAME(mm,DATEADD(mm,2,0)) UNION ALL
SELECT monthindex = 4, month_name = DATENAME(mm,DATEADD(mm,3,0)) UNION ALL
SELECT monthindex = 5, month_name = DATENAME(mm,DATEADD(mm,4,0)) UNION ALL
SELECT monthindex = 6, month_name = DATENAME(mm,DATEADD(mm,5,0)) UNION ALL
SELECT monthindex = 7, month_name = DATENAME(mm,DATEADD(mm,6,0)) UNION ALL
SELECT monthindex = 8, month_name = DATENAME(mm,DATEADD(mm,7,0)) UNION ALL
SELECT monthindex = 9, month_name = DATENAME(mm,DATEADD(mm,8,0)) UNION ALL
SELECT monthindex = 10, month_name = DATENAME(mm,DATEADD(mm,9,0)) UNION ALL
SELECT monthindex = 11, month_name = DATENAME(mm,DATEADD(mm,10,0)) UNION ALL
SELECT monthindex = 12, month_name = DATENAME(mm,DATEADD(mm,11,0))
)
SELECT @x = monthindex,
@y = month_name
FROM Months
ORDER BY monthindex
SET @i = @i + 1
END
--END WHILE
PRINT 'Union all CTE milliseconds ' + CAST(DATEDIFF(ms, @StartTime , GETDATE()) AS varchar(13))
SET @StartTime = GETDATE()
SET @i = 0
WHILE @i < @MaxIterations
BEGIN
SELECT @x = N ,
@y = DATENAME(MONTH,DATEADD(MONTH,N,0)- 1) -- function to list the monthname.
FROM dbo.Tally
WHERE N <= 12
ORDER BY N
SET @i = @i + 1
END
--END WHILE
PRINT 'Tally milliseconds ' + CAST(DATEDIFF(ms, @StartTime , GETDATE()) AS varchar(13))
and got these results:
Recursive CTE milliseconds 3193
Union all CTE milliseconds 416
Tally milliseconds 290
So the Tally is fastest
March 13, 2012 at 10:43 am
Jonathan AC Roberts (3/13/2012)
Just to end arguments over performance I tried this:
DECLARE @StartTime datetimeDECLARE @x int
DECLARE @y varchar(20)
DECLARE @MaxIterations int
SET @MaxIterations = 10000
SET @StartTime = GETDATE()
DECLARE @i int
SET @i = 0
WHILE @i < @MaxIterations
BEGIN
;WITH CTEMonth
AS
(
SELECT 1 AS MonNum
UNION ALL
SELECT MonNum + 1 -- add month number to 1 recursively
FROM CTEMonth
WHERE MonNum < 12 -- just to restrict the monthnumber upto 12
)
SELECT
@x = MonNum
,@y = DATENAME(MONTH,DATEADD(MONTH,MonNum,0)- 1) -- function to list the monthname.
FROM CTEMonth
SET @i = @i + 1
END
--END WHILE
PRINT 'Recursive CTE milliseconds ' + CAST(DATEDIFF(ms, @StartTime , GETDATE()) AS varchar(13))
SET @StartTime = GETDATE()
SET @i = 0
WHILE @i < @MaxIterations
BEGIN
;WITH Months AS
(
SELECT monthindex = 1, month_name = DATENAME(mm,DATEADD(mm,0,0)) UNION ALL
SELECT monthindex = 2, month_name = DATENAME(mm,DATEADD(mm,1,0)) UNION ALL
SELECT monthindex = 3, month_name = DATENAME(mm,DATEADD(mm,2,0)) UNION ALL
SELECT monthindex = 4, month_name = DATENAME(mm,DATEADD(mm,3,0)) UNION ALL
SELECT monthindex = 5, month_name = DATENAME(mm,DATEADD(mm,4,0)) UNION ALL
SELECT monthindex = 6, month_name = DATENAME(mm,DATEADD(mm,5,0)) UNION ALL
SELECT monthindex = 7, month_name = DATENAME(mm,DATEADD(mm,6,0)) UNION ALL
SELECT monthindex = 8, month_name = DATENAME(mm,DATEADD(mm,7,0)) UNION ALL
SELECT monthindex = 9, month_name = DATENAME(mm,DATEADD(mm,8,0)) UNION ALL
SELECT monthindex = 10, month_name = DATENAME(mm,DATEADD(mm,9,0)) UNION ALL
SELECT monthindex = 11, month_name = DATENAME(mm,DATEADD(mm,10,0)) UNION ALL
SELECT monthindex = 12, month_name = DATENAME(mm,DATEADD(mm,11,0))
)
SELECT @x = monthindex,
@y = month_name
FROM Months
ORDER BY monthindex
SET @i = @i + 1
END
--END WHILE
PRINT 'Union all CTE milliseconds ' + CAST(DATEDIFF(ms, @StartTime , GETDATE()) AS varchar(13))
SET @StartTime = GETDATE()
SET @i = 0
WHILE @i < @MaxIterations
BEGIN
SELECT @x = N ,
@y = DATENAME(MONTH,DATEADD(MONTH,N,0)- 1) -- function to list the monthname.
FROM dbo.Tally
WHERE N <= 12
ORDER BY N
SET @i = @i + 1
END
--END WHILE
PRINT 'Tally milliseconds ' + CAST(DATEDIFF(ms, @StartTime , GETDATE()) AS varchar(13))
and got these results:
Recursive CTE milliseconds 3193Union all CTE milliseconds 416
Tally milliseconds 290
So the Tally is fastest
These figures are ballpark-similar to the figures obtained during Jeff Moden et al's epic split function test a couple of years ago.
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
March 13, 2012 at 10:49 am
Jonathan AC Roberts (3/13/2012)What if you want it in a different language?
That is why you would use either the method I used above. Or monthname = DATENAME(mm,DATEADD(mm,0,0)) instead of monthname = 'January' so that the users connected language it used as the output.
March 13, 2012 at 10:57 am
That's assuming you have an order for each month. Additionally, his solution could perform faster depending on the number of rows in the "order" table.
The only thing I would suggest is throw this script in a table-valued function and select from it. 😀
March 13, 2012 at 10:59 am
Users generally want to see it this way. Remember, we get it, but they generally do not!! 😛
Viewing 15 posts - 46 through 60 (of 129 total)
You must be logged in to reply to this topic. Login to reply