March 13, 2012 at 3:05 am
Nice thoughts and suggestions!! But My idea is to keep the SQL script very simple, instead of creating the monthname statically we can go with the dynamic way.
Thanks,
Karthik
March 13, 2012 at 3:09 am
The recursive CTE is used for generating rows - and it's a massively expensive way to do this. In 2K8 or higher, row constructors would be much cheaper
VALUES((1),(2),...)
In 2k5, a simple CTE would be much cheaper:
SELECT 1 AS n UNION ALL SELECT 2 ...
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 3:09 am
Krtyknm (3/13/2012)
Nice thoughts and suggestions!! But My idea is to keep the SQL script very simple, instead of creating the monthname statically we can go with the dynamic way.
Euh...
keep the SQL script very simple <> recursive CTE
At least for most people that is true...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 3:14 am
I'm not sure dynamic is needed, since month names are not really going to change. I can think of eaiser ways to "localise" a site than that.
March 13, 2012 at 3:18 am
How about using the order by converting the month to a date...
create table months (MonthName varchar (12))
insert months values ('January')
insert months values ('February')
insert months values ('March')
insert months values ('April')
insert months values ('May')
insert months values ('June')
insert months values ('July')
insert months values ('August')
insert months values ('September')
insert months values ('October')
insert months values ('November')
insert months values ('December')
select * from months order by convert(date, '1 ' + monthName + ' 1900')
March 13, 2012 at 3:25 am
Goodness me, if you are going to type them all in, SSRS has a place for you to do that much more easily for any parameter list. 😉
If you want something general purpose to create a set of rows from nothing, then the original post is a good example.
March 13, 2012 at 3:28 am
An option:
SELECT DATENAME(month, DATEADD(month, [number], 0)) [MonthName]
, MONTH(DATEADD(month, [number], 0)) [MonthValue]
FROM [master].[dbo].[spt_values]
WHERE [type]='P'
AND [number] BETWEEN 0 AND 11
ORDER BY [number];
March 13, 2012 at 3:32 am
Koen Verbeeck (3/13/2012)
And what if February doesn't have any data?
Unless I am mistaken he had year and month separated so it wouldn't work checking for it until they are combined as input in this case.
March 13, 2012 at 3:33 am
dennis.hafstrom (3/13/2012)
Koen Verbeeck (3/13/2012)
And what if February doesn't have any data?Unless I am mistaken he had year and month separated so it wouldn't work checking for it until they are combined as input in this case.
My point exactly. Now the end user can call up a report that doesn't contain any data.
Frustrating 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 3:54 am
Hi you all.
Well, I prefer to use a tally table. more clean.
March 13, 2012 at 3:56 am
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 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 4:28 am
Re "The same SQL statement should also be reusable for other projects. "
On any Production-level database, that would be enough reason to make sure you definitely did have a reference table. Say Ref_CalenderMonth_tbl (MonthNum tinyint, MonthName varchar(20))
create procedure dbo.ListMonthNames_sp
as
select MonthNum , MonthName
from Ref_CalenderMonth_tbl
order by MonthNum
go
EXEC dbo.ListMonthNames_sp
go
Also, sooner or later, any system auditing or exception reporting will want to know about months that have no data. In which case, something like Ref_CalenderMonth_tbl becomes essential.
This is such a small table, performance should not be a worry. In any case, if the same stored procedure is being used by several systems, SQL Server is likely to cache the results anyway.
March 13, 2012 at 4:37 am
If you insist on not using a calendar table, then this is better: -
WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
CTEMonth(MonNum) AS (SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t2 x, t2 y)
SELECT MonNum,
DATENAME(MONTH,DATEADD(MONTH,MonNum,0)- 1)[MonthName] -- function to list the monthname.
FROM CTEMonth;
Proof?
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT REPLICATE('-',80);
PRINT 'Recursive CTE';
PRINT REPLICATE('-',80);
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 TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT REPLICATE('-',80);
PRINT 'Non-Recursive CTE';
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
CTEMonth(MonNum) AS (SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t2 x, t2 y)
SELECT MonNum,
DATENAME(MONTH,DATEADD(MONTH,MonNum,0)- 1)[MonthName] -- function to list the monthname.
FROM CTEMonth;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------------------------------------------------------------------------------
Recursive CTE
--------------------------------------------------------------------------------
Table 'Worktable'. Scan count 2, logical reads 73, 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.
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------------------------------------------------------------------------------
Non-Recursive CTE
--------------------------------------------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Yes, there isn't much in it but every little bit counts.
March 13, 2012 at 4:42 am
I have to agree with Cadavre here.
It's not just the performance in this case, rCTE's are expensive and shouldn't be used unless they're doing far more work than simply generating a set of rows.
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 4:59 am
For the purpose of displaying 12 values the use of a recursive CTE is fine. Performance won't be an issue. You are using the CTE to generate numbers 1 to 12. For the same thing you could use a tally table for example Jeff Moden's: http://www.sqlservercentral.com/articles/T-SQL/62867/. Then you would end up with equivalent:SELECT N MonNon,
DATENAME(MONTH,DATEADD(MONTH,N,0)- 1)[MonthName] /* function to list the monthname. */
FROM dbo.Tally
WHERE N <= 12 ORDER BY N
Viewing 15 posts - 16 through 30 (of 129 total)
You must be logged in to reply to this topic. Login to reply