Convert a range into a sequence without using a function

sql-server, t-sql

Solution

Here is one way to try (full example, runnable as-is):

-- Dummy data
DECLARE @Data TABLE (FromKey INTEGER, ToKey INTEGER, Value VARCHAR(10))
INSERT @Data VALUES (1,4,'AAA'),(5,6,'BBB')

-- table of numbers, 1-100 for demo purposes
DECLARE @Numbers TABLE (Num INTEGER PRIMARY KEY)
INSERT @Numbers
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY object_id)
FROM sys.objects

SELECT n.Num, d.Value
FROM @Data d
    JOIN @Numbers n ON d.FromKey <= n.Num AND d.ToKey >= n.Num

What I would do, is create a physical "Numbers" table in your database, and populate with numbers from 1 to n, where n is a large enough number to cover your needs. This would be a one off table/data creation - but then the table can be used for purposes like the above.

Problem

Having a table like this: ``` FromKey | ToKey | Value ------------------------ 1 | 4 | AAA 5 | 6 | BBB ``` what is the most efficient way to get the following result? ``` Key | Value ----------------- 1 | AAA 2 | AAA 3 | AAA 4 | AAA 5 | BBB 6 | BBB ``` I know how to do it using a table function and CROSS APPLY, but that approach is slow for big tables. I wonder if there is a faster solution.

Original source