matt32 (3/31/2010)
for my needs it is a great opportunity to create a view from this sqlWITH mycte AS (SELECT cast ('2007-01-01' AS DATETIME) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue +
1 < getdate())
SELECT distinct YEAR(DateValue) MyYear,MONTH(DateValue) MyMonth
from mycte OPTION ( MAXRECURSION 0 )
Hey, that's great, but only if performance is not important to you 🙂
For a good explanation of why RBAR techniques like this can really suck, see MVP Jeff Moden's article[/url] on this site.
For comparison, let's do the same thing, but in a set-based manner:
-- For demonstration purposes only
USE tempdb;
GO
-- Conditional object drops
IF OBJECT_ID(N'dbo.MonthYearList')
IS NOT NULL
DROP FUNCTION dbo.MonthYearList;
GO
IF OBJECT_ID(N'dbo.Numbers')
IS NOT NULL
DROP FUNCTION dbo.Numbers;
GO
-- An in-line function to create a numbers table on the fly
CREATE FUNCTION dbo.Numbers
(@N BIGINT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH
N1 AS (SELECT N = 1 UNION ALL SELECT 1),
N2 AS (SELECT N = 1 FROM N1 T, N1),
N3 AS (SELECT N = 1 FROM N2 T, N2),
N4 AS (SELECT N = 1 FROM N3 T, N3),
N5 AS (SELECT N = 1 FROM N4 T, N4),
N6 AS (SELECT N = 1 FROM N5 T, N5),
NM AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N FROM N6)
SELECT N
FROM NM
WHERE @N >= 0
AND N <= @N
GO
-- An in-line function wrapping the set-based implementation
CREATE FUNCTION dbo.MonthYearList
(@From DATETIME)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT the_year = YEAR(This.month_start),
the_month = MONTH(This.month_start)
FROM dbo.Numbers (DATEDIFF(MONTH, @From, CURRENT_TIMESTAMP) + 1)
CROSS
APPLY (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)) Base (month_start)
CROSS
APPLY (SELECT DATEADD(MONTH, 1 - N, Base.month_start)) This (month_start)
WHERE This.month_start BETWEEN @From AND CURRENT_TIMESTAMP;
GO
-- Collect performance statistics
SET STATISTICS IO, TIME ON;
-- The RBAR method
WITH mycte AS (SELECT cast ('1753-01-01' AS DATETIME) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue +
1 < getdate())
SELECT distinct YEAR(DateValue) MyYear,MONTH(DateValue) MyMonth
from mycte OPTION ( MAXRECURSION 0 )
-- Set based
SELECT *
FROM dbo.MonthYearList ('17530101');
-- Stop recording statistics
SET STATISTICS IO, TIME OFF;
GO
-- Tidy up
DROP FUNCTION dbo.MonthYearList;
DROP FUNCTION dbo.Numbers;
Performance results:
Your method: 2152ms
Set-based: 168ms
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi