What short query will return an vector (a one-column column table) representing a set of integers from x1 to x2?

Question

Assuming we have no actual table to select the data from, what do we need to SELECT to return a table consisting of one integer column containing all integer numbers starting with x1 and finishing with x2 and sorted in ascending order?

Answer 1

You've always a table with data in SQL Server to use a base

WITH cDummy AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY c1.id) AS number
    FROM
       sys.columns c1 CROSS JOIN  sys.columns c2 CROSS JOIN  sys.columns c3
)
SELECT
    number
FROM
    cDummy
WHERE
    number BETWEEN @x AND @x * 2

Answer 2

you can use this function (it is based on Itzik Ben-Gan's function from an interview I did with him here: Interview With Itzik Ben-Gan Author Of Inside Microsoft SQL Server 2005: T-SQL Querying)

CREATE FUNCTION dbo.fn_numbers(@Start AS BIGINT,@End AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums 
  WHERE n between  @Start and @End;  
GO  

Here is how you run it

-- Test function 
SELECT * FROM dbo.fn_numbers(1,100) AS F;  
GO

-- Test function 
SELECT * FROM dbo.fn_numbers(200,700) AS F;  
GO   

Answer 3

One option, (in T-SQL)

  Declare @X1 Integer Set @x1 = 45
  Declare @X2 Integer Set @x2 = 87
  Declare @I Integer Set @I = @x1
  Declare @Outs Table (val integer Primary Key Not Null)
  While @I <= @x2 Begin
    Insert @Outs(Val) Values (@I)
    Set @I = @I + 1
  End
  Select Val From @Outs