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).