Creating Sequence in Sql Server

Question

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

Answer 1

All that you need to simulate sequence objects in SQL Server.

Answer 2

Here is some code that works in 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

Adapted from this: Dr. Tom's Workshop: Generating Sequence Numbers

Answer 3

As of SQL Server 2012, sequences have been added and are supposedly faster than IDENTITY.

Quick example using CYCLE option as the question requests but most people probably will not use CYCLE because they'll use it for a synthetic primary key.

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

Here's the SQL syntax taken from 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 } ]  
    [ ; ]  

Here's how to do it in the GUI. This is the equivolent of the quick example above without the CYCLE option checked because most poeple won't use that:

1. In the Object Explorer, expand the Programmability folder
2. Under the Programmability folder, right click on the Sequences folder as shown below:

3. Underlined are the values that you would update to get the equivalent of the SQL statement above, however, I would consider changing these depending on your needs (see notes below).

Notes:

• The default Start value, Minimum value, and Maximum value were determined by the range of the data type which was an int in this case. See here for more info on sequences and ranges.
• Pretty decent chance you'll want your sequence to start at 1 and you might want you minimum value as 1, too.

Answer 4

There is 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 only put a 100K of numbers in my table but you might need more. You need only populate this table once.Now you can create any sequence you desire. Like so:

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

Want an increment value?:

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