Creating Sequence on Sql Server

Question

I want to create a sequence of numbers in SQL Server that will have a minimum value and a maximum value. I want to execute a loop if the number reaches the maximum limit. Can anyone help me out?

Answer 1

No need for a while loop. First, you need a Tally or Numbers table:

Create Table dbo.Numbers ( Value int not null Primary Key Clustered )
GO
With Nums As
    (
    Select Row_Number() Over( Order By S1.object_id ) As Num
    From sys.columns as s1
        cross join sys.columns as s2
    )
Insert dbo.Numbers( Value )
Select Num
From Nums
Where Num <= 100000

I put only 100 thousand numbers in the table, but you may need more. You only need to fill out this table once. Now you can create any sequence you want. For example:

Select Value
From dbo.Numbers
Where Value Between @Start And @End

Want to get the increment value ?:

Select Value
From dbo.Numbers
Where Value % @Increment = 0

Answer 2

, SQL Server.

Answer 3

, SQL 2000:

DECLARE @NumRows int;
SET @NumRows = 10000;
DECLARE @t table (RowNum int not null primary key);

-- Seed
INSERT @t VALUES(1)

WHILE @@ROWCOUNT > 0
BEGIN
    INSERT @t
     SELECT     t.RowNum + x.MaxRowNum FROM @t t
     CROSS JOIN 
     (SELECT    MAX(RowNum) MaxRowNum FROM @t) x
     WHERE      t.RowNum <= @NumRows - x.MaxRowNum
END

: Dr. :

Answer 4

SQL Server 2012, , IDENTITY.

CYCLE , , , CYCLE, .

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 
CYCLE;

SQL MSDN:

CREATE SEQUENCE [schema_name . ] sequence_name  
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]  
    [ START WITH <constant> ]  
    [ INCREMENT BY <constant> ]  
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <constant> ] } | { NO CACHE } ]  
    [ ; ]  

. CYCLE, poeple :

• Programmability
• Programmability :

3. , , SQL, (. ).

:

• , , int. . .
• , , 1, 1.