Creating Sequence in Sql Server

Question

I want to create a sequence of numbers in sql server it will have the 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

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