Create a Numbers Table in MS SQL Server 2005 or SQL2008 Databases
With the implementation of recursive sql using CTE (Common Table Expression) in SQL server 2005, SQL developers can now create numbers table easily in MS SQL Server 2005 as well as SQL Server 2008 databases using recursive functions.
I some times experienced cases where I need a numbers table which includes in a sequence of numbers from a beginning number to an ending number.
Between these two boudary numbers I needed all the numbers in order.
What ever is your reason for a Numbers Table you can create Numbers Table using recursive function like Common Table Expression (CTE) recursive t-sql query.
SELECT * FROM dbo.NumbersTable(1,12,1)
In fact we have a work-around and can use an existing system table master..spt_values for our aim by adding some criterias in the SELECT query as follows.
SELECT number FROM master..spt_values
WHERE Type = 'P' ORDER BY Number
But spt_values system table will help us to get a list of numbers from 0 to 2047.
Of course you can manipulate the outcome by adding 1 for instance to get a sequence of numbers from 1 to 2048.
Or even add some functions which will enlarge the outcome from the spt_values.
But I believe, an ad-hoc table which will be created on-demand and then will be removed from memory might be better for wide range of numbers.
The user-defined function dbo.NumbersTable creates a sequence of numbers between two numbers that you can pass these boundary values as parameters.
And also you can set the step values between each values.
Here is the t-sql source code of dbo.NumbersTable user defined inline table-valued function.
CREATE FUNCTION NumbersTable (
@fromNumber int,
@toNumber int,
@byStep int
) RETURNS TABLE
RETURN (
WITH CTE_NumbersTable AS (
SELECT @fromNumber AS i
UNION ALL
SELECT i + @byStep
FROM CTE_NumbersTable
WHERE
(i + @byStep) <= @toNumber
)
SELECT * FROM CTE_NumbersTable
)
I updated first version of the numbers table function source code with the below SQL function codes because of the "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." message
I've set the MAXRECURSION option to 0 in order to let recursive function loop without any limit.
CREATE FUNCTION NumbersTable (
@fromNumber int,
@toNumber int,
@byStep int
)
RETURNS @NumbersTable TABLE (i int)
AS
BEGIN
WITH CTE_NumbersTable AS (
SELECT @fromNumber AS i
UNION ALL
SELECT i + @byStep
FROM CTE_NumbersTable
WHERE
(i + @byStep) <= @toNumber
)
INSERT INTO @NumbersTable
SELECT i FROM CTE_NumbersTable OPTION (MAXRECURSION 0)
RETURN;
END
You can use this recursive t-sql user defined function to create an auxilary SQL Server database table which has a sequence of numbers.
If you want to build a dates table again using a recursive function in SQL Server, you can read the article and use the scripts at Create a Date Table or a SQL Server Calendar Table using CTE T-SQL Code.