SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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)

Sample T-SQL Numbers Table for developers

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.






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems