As a SQL Server developer, you may come across situations where you need to generate a range of values between two integers. This can be a common requirement when working with data manipulation or reporting tasks. However, the approach you take to generate this range can greatly impact the performance of your code.
In this article, we will explore different methods of generating a range of values in SQL Server and discuss their efficiency. We will compare three different scripts and evaluate their performance based on the number range 5 to 567,720.
Method 1: Looping with a Table Variable
The first method involves using a loop to insert records into a table variable. This approach is simple and efficient for small number ranges. However, as the range gets larger, the execution time increases significantly.
CREATE FUNCTION dbo.fnRange (
@first int,
@last int
)
RETURNS @values TABLE (value int primary key)
AS
BEGIN
DECLARE @temp int
IF @first > @last
BEGIN
SET @temp = @first
SET @first = @last
SET @last = @temp
END
WHILE @first <= @last
BEGIN
INSERT INTO @values
VALUES (@first)
SET @first = @first + 1
END
RETURN
END
While this method is straightforward, it becomes inefficient for larger number ranges.
Method 2: Cross Joining with a View
The second method involves cross joining a view that contains the digits 0 to 9 to generate the range of values. This approach leverages the set-based operations of SQL Server and is more efficient for larger number ranges.
CREATE VIEW dbo.Digits AS
SELECT 0 AS value
UNION ALL SELECT 1 AS value
UNION ALL SELECT 2 AS value
UNION ALL SELECT 3 AS value
UNION ALL SELECT 4 AS value
UNION ALL SELECT 5 AS value
UNION ALL SELECT 6 AS value
UNION ALL SELECT 7 AS value
UNION ALL SELECT 8 AS value
UNION ALL SELECT 9 AS value
CREATE FUNCTION dbo.fnCrossJoinRange (
@first int,
@last int
)
RETURNS @values TABLE (value int primary key)
AS
BEGIN
INSERT INTO @values (value)
SELECT num = units.value + (tens.value * 10) + (hundreds.value * 100) + (Thousands.value * 1000) + (TenThousands.value * 10000) + (CThousands.value * 100000) + (Millions.value * 1000000)
FROM dbo.Digits units
CROSS JOIN dbo.Digits tens
CROSS JOIN dbo.Digits hundreds
CROSS JOIN dbo.Digits Thousands
CROSS JOIN dbo.Digits TenThousands
CROSS JOIN dbo.Digits CThousands
CROSS JOIN dbo.Digits Millions
WHERE units.value + (tens.value * 10) + (hundreds.value * 100) + (Thousands.value * 1000) + (TenThousands.value * 10000) + (CThousands.value * 100000) + (Millions.value * 1000000) BETWEEN @first AND @last
RETURN
END
This method is more efficient than the previous one, especially for larger number ranges. It takes advantage of set-based operations and generates the range of values without the need for a loop.
Method 3: Combining Filtering Techniques
The third method combines the filtering techniques used in the previous methods to limit the number of records generated. This approach pre-selects values before applying a final WHERE clause, resulting in improved performance.
CREATE FUNCTION dbo.fnUnionRange (
@first int,
@last int
)
RETURNS @values TABLE (value int primary key)
AS
BEGIN
INSERT INTO @values (value)
SELECT units.value + tens.value + hundreds.value + Thousands.value + TenThousands.value + CThousands.value + Millions.value AS list
FROM (
SELECT 0 AS value
UNION ALL SELECT 1 AS value WHERE 1 <= @last
UNION ALL SELECT 2 AS value WHERE 2 <= @last
UNION ALL SELECT 3 AS value WHERE 3 <= @last
UNION ALL SELECT 4 AS value WHERE 4 <= @last
UNION ALL SELECT 5 AS value WHERE 5 <= @last
UNION ALL SELECT 6 AS value WHERE 6 <= @last
UNION ALL SELECT 7 AS value WHERE 7 <= @last
UNION ALL SELECT 8 AS value WHERE 8 <= @last
UNION ALL SELECT 9 AS value WHERE 9 <= @last
) AS Units,
(
SELECT 0 AS value
UNION ALL SELECT 10 AS value WHERE 10 <= @last
UNION ALL SELECT 20 AS value WHERE 20 <= @last
UNION ALL SELECT 30 AS value WHERE 30 <= @last
UNION ALL SELECT 40 AS value WHERE 40 <= @last
UNION ALL SELECT 50 AS value WHERE 50 <= @last
UNION ALL SELECT 60 AS value WHERE 60 <= @last
UNION ALL SELECT 70 AS value WHERE 70 <= @last
UNION ALL SELECT 80 AS value WHERE 80 <= @last
UNION ALL SELECT 90 AS value WHERE 90 <= @last
) AS Tens,
(
SELECT 0 AS value
UNION ALL SELECT 100 AS value WHERE 100 <= @last
UNION ALL SELECT 200 AS value WHERE 200 <= @last
UNION ALL SELECT 300 AS value WHERE 300 <= @last
UNION ALL SELECT 400 AS value WHERE 400 <= @last
UNION ALL SELECT 500 AS value WHERE 500 <= @last
UNION ALL SELECT 600 AS value WHERE 600 <= @last
UNION ALL SELECT 700 AS value WHERE 700 <= @last
UNION ALL SELECT 800 AS value WHERE 800 <= @last
UNION ALL SELECT 900 AS value WHERE 900 <= @last
) AS Hundreds,
(
SELECT 0 AS value
UNION ALL SELECT 1000 AS value WHERE 1000 <= @last
UNION ALL SELECT 2000 AS value WHERE 2000 <= @last
UNION ALL SELECT 3000 AS value WHERE 3000 <= @last
UNION ALL SELECT 4000 AS value WHERE 4000 <= @last
UNION ALL SELECT 5000 AS value WHERE 5000 <= @last
UNION ALL SELECT 6000 AS value WHERE 6000 <= @last
UNION ALL SELECT 7000 AS value WHERE 7000 <= @last
UNION ALL SELECT 8000 AS value WHERE 8000 <= @last
UNION ALL SELECT 9000 AS value WHERE 9000 <= @last
) AS Thousands,
(
SELECT 0 AS value
UNION ALL SELECT 10000 AS value WHERE 10000 <= @last
UNION ALL SELECT 20000 AS value WHERE 20000 <= @last
UNION ALL SELECT 30000 AS value WHERE 30000 <= @last
UNION ALL SELECT 40000 AS value WHERE 40000 <= @last
UNION ALL SELECT 50000 AS value WHERE 50000 <= @last
UNION ALL SELECT 60000 AS value WHERE 60000 <= @last
UNION ALL SELECT 70000 AS value WHERE 70000 <= @last
UNION ALL SELECT 80000 AS value WHERE 80000 <= @last
UNION ALL SELECT 90000 AS value WHERE 90000 <= @last
) AS TenThousands,
(
SELECT 0 AS value
UNION ALL SELECT 100000 AS value WHERE 100000 <= @last
UNION ALL SELECT 200000 AS value WHERE 200000 <= @last
UNION ALL SELECT 300000 AS value WHERE 300000 <= @last
UNION ALL SELECT 400000 AS value WHERE 400000 <= @last
UNION ALL SELECT 500000 AS value WHERE 500000 <= @last
UNION ALL SELECT 600000 AS value WHERE 600000 <= @last
UNION ALL SELECT 700000 AS value WHERE 700000 <= @last
UNION ALL SELECT 800000 AS value WHERE 800000 <= @last
UNION ALL SELECT 900000 AS value WHERE 900000 <= @last
) AS CThousands,
(
SELECT 0 AS value
UNION ALL SELECT 1000000 AS value WHERE 1000000 <= @last
UNION ALL SELECT 2000000 AS value WHERE 2000000 <= @last
UNION ALL SELECT 3000000 AS value WHERE 3000000 <= @last
UNION ALL SELECT 4000000 AS value WHERE 4000000 <= @last
UNION ALL SELECT 5000000 AS value WHERE 5000000 <= @last
UNION ALL SELECT 6000000 AS value WHERE 6000000 <= @last
UNION ALL SELECT 7000000 AS value WHERE 7000000 <= @last
UNION ALL SELECT 8000000 AS value WHERE 8000000 <= @last
UNION ALL SELECT 9000000 AS value WHERE 9000000 <= @last
) AS Millions
WHERE units.value + tens.value + hundreds.value + Thousands.value + TenThousands.value + CThousands.value + Millions.value BETWEEN @first AND @last
RETURN
END
This method combines the efficiency of set-based operations with the filtering techniques used in the previous methods, resulting in improved performance.
Conclusion
When generating a range of values in SQL Server, it is important to consider the efficiency of your code. Set-based operations are generally more efficient than looping, especially for larger number ranges. The second and third methods discussed in this article leverage set-based operations and filtering techniques to generate the range of values efficiently.
While the first method is simple and efficient for small number ranges, it becomes increasingly slower as the range gets larger. The second and third methods offer better performance for larger number ranges, with the third method combining the efficiency of set-based operations with the filtering techniques used in the second method.
By choosing the most efficient method for generating a range of values in SQL Server, you can optimize the performance of your code and improve the overall efficiency of your database operations.