Integer Range Table Function

Overview

Having participated in SQL Server forums for a while, I have come across a question asking whether there was any built-in table in SQL Server, whether a temporary table or a system table, that contains just integer values from 1 to any given number.

Unfortunately, there is no temporary table or system table available in SQL Server, and if I am not mistaken even in other database engines, that contains this data. The best way to generate such data is not by making use of a temporary table but by making use of a table-valued user-defined function.

Function Definition

The following user-defined function accepts as input an integer value that will serve as the maximum value in the returned integer table.

CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MaxValue INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
    DECLARE @Index    INT
    SET @Index = 1
    WHILE @Index <= @MaxValue
    BEGIN
        INSERT INTO @Integers ( [IntValue] ) VALUES ( @Index )
        SET @Index = @Index + 1
    END

    RETURN
END
GO

Description

The user-defined function is quite straight-forward. It simply inserts into the return TABLE variable (@Integers) the integer values from 1 to the maximum integer value supplied in the parameter using a WHILE loop. If the value supplied in the parameter is less than 1, then the table returned will not contain any records.

To use this function, you can simply issue a simple SELECT statement and using this in the FROM clause of the SELECT, as follows:

SELECT * FROM [dbo].[ufn_GenerateIntegers] ( 1000 )

This will produce a result set containing one column called [IntValue] with records containing values from 1 through 1000.

To give some flexibility to the user-defined function, it would be a good idea to include the starting value in the parameter instead of having it always start with 1. The following user-defined function performs the same task as above but includes a parameter for the starting value (@MinValue).

CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MinValue INT, @MaxValue INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
    WHILE @MinValue <= @MaxValue
    BEGIN
        INSERT INTO @Integers ( [IntValue] ) VALUES ( @MinValue )
        SET @MinValue = @MinValue + 1
    END

    RETURN
END
GO

To use this user-defined function, you simpy have to supply the starting value and ending value as parameters, as follows:

SELECT * FROM [dbo].[ufn_GenerateIntegers] ( 100, 500 )

This will produce a result set containing one column called [IntValue] with records containing values from 100 through 500.

Alternative Approach

Here's a different approach to producing the same integer-table output. It minimizes the number of loops performed and makes use of a CROSS JOIN of tables.

CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MaxValue INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
    DECLARE @Digits TABLE ( [Digit] INT )
    DECLARE @Counter INT

    SET @Counter = 0
    WHILE @Counter < 10
    BEGIN
        INSERT INTO @Digits ( [Digit] ) VALUES ( @Counter )
        SET @Counter = @Counter + 1
    END

    INSERT INTO @Integers ( [IntValue] )
    SELECT (Thousands.Digit * 1000) + (Hundreds.Digit * 100) + 
           (Tens.Digit * 10) + Ones.Digit
    FROM @Digits Thousands, @Digits Hundreds, @Digits Tens, @Digits Ones
    WHERE (Thousands.Digit * 1000) + (Hundreds.Digit * 100) + 
          (Tens.Digit * 10) + Ones.Digit BETWEEN 1 AND @MaxValue
    ORDER BY 1

    RETURN
END
GO

The maximum value returned by this version of the user-defined function is 9999 but it can easily be extended to accommodate up to any number. The concept behind this approach is that each digit within the output, basically the ones, tens, hundreds and thousands place is made up of the numbers from 0 to 9. So the table variable created only contains 10 records, one for each number, and only 10 loops are executed. The output table is then populated by joining the same table variable to itself once for each position. From the user-defined function above, there were 4 tables that were CROSS-JOINed to produce the output table, each digit represented by one table variable (Ones, Tens, Hundreds and Thousands).

Usage

One possible use of this user-defined function is in identifying missing IDENTITY values in a table. Let's say you have the following table which contains an IDENTITY column and with the following sample data:

-- Step #1: Create Table and Populate with Values
CREATE TABLE #MissingID ( [ID] INT IDENTITY, [Name] VARCHAR(20) )

INSERT INTO #MissingID ( [Name] ) VALUES ( 'Bentley' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'BMW' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Ferrari' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Lamborghini' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Hummer' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Jaguar' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Lexus' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Mercedes Benz' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Porsche' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Volvo' )

SELECT * FROM #MissingID

The output of the SELECT statement will be as follows:

ID	Name
1	Bentley
2	BMW
3	Ferrari
4	Lamborghini
5	Hummer
6	Jaguar
7	Lexus
8	Mercedes Benz
9	Porsche
10	Volvo

Let's say certain records have been deleted from the table, as shown in the following script:

-- Step #2: Delete IDs
DELETE FROM #MissingID WHERE [ID] IN (3, 4, 9)

SELECT * FROM #MissingID

The table now has the following records:

ID	Name
1	Bentley
2	BMW
5	Hummer
6	Jaguar
7	Lexus
8	Mercedes Benz
10	Volvo

To identify the missing IDENTITY values, in this case the deleted IDs 3, 4 and 9, we can make use of the Integer Range Table user-defined function to generate a table of integers to be used to join with our table as shown in the following script:

-- Step #3: Identify Missing IDENTITY Values
DECLARE @MaxID INT
SELECT @MaxID = [ID] FROM #MissingID

SELECT A.*
FROM [dbo].[ufn_GenerateIntegers] ( @MaxID ) A LEFT OUTER JOIN #MissingID B
  ON A.[IntValue] = B.[ID]
WHERE B.[ID] IS NULL

The first step is to determine the highest IDENTITY value that have been used in the table (SELECT @MaxID = [ID] FROM #MissingID). This maximum value is then passed as a parameter to the user-defined function. We will be making use of the first version of the user-defined function where only one parameter is expected. Then the table generated by the user-defined function, which contains values from 1 to the maximum ID of our table, is then LEFT JOINed with our table to identify the missing IDs (WHERE B.[ID] IS NULL).