SQL with numbers

Question

I am trying to create a loop with SQL Server.

When executed, SELECT 1 UNION SELECT 2 UNION SELECT 3;SQL Server creates a table with 1 column and 4 rows.

Can anyone think of how to achieve this, but not using it UNION?

Answer 1

You can use the table constructor values():

select *
from (values (1), (2), (3), (4)) v(n);

Answer 2

You can do this easily with recursive cte.

declare @max int=4

;with cte as
(
    select 1 as val
    union all
    select val+1
    from cte
    where val < @max
)
select *
from cte

Answer 3

, ...

...

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

,

 CREATE FUNCTION [dbo].[fnTallyProg]
/**********************************************************************************************************************
 Purpose:
 Given a start value, end value, and increment, create a sequencial list of integers.
 Programmers Notes:
 1. The increment can be negative if the start value is greater than the end value. In other words, it can count down
    as well as up.

 Revison History:
 Rev 00 - 18 Feb 2017 - Jeff Moden
        - Rewrite original to take start, end, and increment parameters.
**********************************************************************************************************************/
        (
         @pStart     BIGINT
        ,@pEnd       BIGINT
        ,@pIncrement BIGINT
        )
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN WITH 
 E01(N)   AS (SELECT NULL FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))E0(N)) --10 rows
,E04(N)   AS (SELECT NULL FROM E01 a CROSS JOIN E01 b CROSS JOIN E01 c CROSS JOIN E01 d) --10 Thousand rows
,E16(N)   AS (SELECT NULL FROM E04 a CROSS JOIN E04 b CROSS JOIN E04 c CROSS JOIN E04 d) --10 Quadrillion rows, which is crazy
,Tally(N) AS (SELECT TOP (ABS((@pEnd-@pStart+@pIncrement)/@pIncrement))
                     N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                FROM E16
               WHERE (@pStart<=@pEnd AND @pIncrement > 0)
                  OR (@pStart>=@pEnd AND @pIncrement < 0)
               ORDER BY N
             )
      SELECT TOP (ABS((@pEnd-@pStart+@pIncrement)/@pIncrement))
             N = (t.N-1)*@pIncrement+@pStart
        FROM Tally t
       ORDER BY t.N
;
GO

, JL

CREATE FUNCTION [dbo].[tfn_Tally]
(
@NumOfRows BIGINT = 1000000 
,@StartWith BIGINT = 1563984
)
/* ============================================================================
07/20/2017 JL, Created. Capable of creating a sequense of rows 
                ranging from -10,000,000,000,000,000 to 10,000,000,000,000,000
============================================================================ */
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN
WITH 
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10 rows
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100 rows
        cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),                             -- 10,000 rows
        cte_n4 (n) AS (SELECT 1 FROM cte_n3 a CROSS JOIN cte_n3 b),                             -- 100,000,000 rows
        cte_Tally (n) AS (
            SELECT TOP (@NumOfRows)
                (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) + @StartWith
            FROM 
                cte_n4 a CROSS JOIN cte_n4 b                                                    -- 10,000,000,000,000,000 rows
            )
    SELECT 
        t.n
    FROM 
        cte_Tally t;

Answer 4

ad-hoc

Declare @N int =4

Select Top (@N) N=Row_Number() Over (Order By (Select NULL)) 
 From  master..spt_values n1

N
1
2
3
4

Answer 5

If you need a sequence of values ​​that will be merged into another table later, I highly recommend creating a one-time auxiliary table of numbers, as described here

The code for creating such a table is quite simple:

SELECT TOP (1000000) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
INTO dbo.Numbers
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(n)

This will create a permanent "Numbers" table in your db, which you can use to join other tables or use functions.