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

Assuming we have no a开发者_开发问答ctual 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?

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   

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

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