dbo.tvf_TallyTable

IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = object_id (N’dbo.tvf_TallyTable’)
AND type IN ( N’FN’, N’IF’, N’TF’) )
BEGIN
DROP FUNCTION dbo.tvf_TallyTable;
END
GO
CREATE FUNCTION dbo.tvf_TallyTable
(
@Low INT,
@High INT
)
RETURNS TABLE WITH SCHEMABINDING
AS
/*
*******************************************************************************
Created By: Barry Blessing (b567123)
——————————————————————————-
Date Created:
——————————————————————————-
Purpose: “Inline” CTE-driven “Tally Table” produces values from 1 to 10,000
Database:
——————————————————————————-
Parameters:
——————————————————————————-
Performance Statistics:
*******************************************************************************
Date Ran:

Test Statements ran:
SELECT * FROM dbo.tvf_TallyTable( 2, 13);
SELECT * FROM dbo.tvf_TallyTable( 0, 1);
SELECT * FROM dbo.tvf_TallyTable( 1, 2);
SELECT * FROM dbo.tvf_TallyTable( 0, CAST( ISNULL(DATEDIFF( month, ‘2016-09-01’, ‘2016-09-30’ ), 1) AS INT) );

Run time in seconds: 0
*******************************************************************************
Maintenance Log:
*******************************************************************************
Date Version PCM Author Comments
——————————————————————————-

*******************************************************************************
*/
RETURN
WITH t1(IndexNo) AS ( SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0) , /* 10E+1 or 10 rows */

t2(IndexNo) AS ( SELECT 0 FROM t1 AS a, t1 AS b ) , /* 10E+2 or 100 rows */

t3(IndexNo) AS ( SELECT 0 FROM t2 AS a, t2 AS b ) , /* 10E+4 or 10,000 rows max */

cteTally(IndexNo) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 )

SELECT IndexNo
FROM cteTally
WHERE IndexNo >= @Low AND IndexNo <= @High;
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s