Often we need to create a sequence of integers or a sequence of date from a starting point to an end point. Suppose we want to create a sequence of integer starting from 10 to 20. We can do this using the following query using row_number() function. Of course it will only work for sql server 2005 and above.
select row_number() over (order by name) as int_seq
from sys.all_objects
To use row_number() function, we need to query over some table. We have taken sys.all_objects table so that we are sure that we are not depending on any table that may not have sufficient number of rows. Of course, our sys.all_objects table may not have that many objects upto our desired end number. In that case we can do some trick if the difference between starting number and end number is not greater than our total object count.What I am trying to say is, suppose we need a sequence from 9000 to 9100. We may not have 9100 objects in our sys.all_objects. But if we have 100 objects, then also we can use this query with a little twick.
select row_number() over (order by name) + 9000 as int_seq
from sys.all_objects
But we are just ensuring the starting point of the sequence. What about the ending point? We can simply use nested query or inner query.
select A.int_seq
from
(
select row_number() over (order by name) + 9000 as int_seq
from sys.all_objects
) A
where A.int_seq >=9000
and A.int_seq <= 9100
This will give us the desired sequence from 9000 to 9100.
One thing we need to keep in mind that it will be able to generate maximum number of items equal to the row count of sys.all_objects table. In any case, if we need to have more number of sequential numbers, then we can join sys.all_objects table twice. This will give us a fairly large number of sequence.
select A.int_seq
from
(
select row_number() over (order by A.name) as int_seq
from sys.all_objects A, sys.all_objects B
) A
Now we can use this sequence number to generate a date sequence also. Sometimes we need to create a date range, for example we need the dates from the current day to the next 20 days. We can achieve this using the following query.
select GETDATE()+A.int_seq
from
(
select ROW_NUMBER() over (order by name) as int_seq
from sys.all_objects
) A
where GETDATE()+A.int_seq <= GETDATE() + 20
A Complete Table-Valued Function for Generating Integer Sequence:
Now to get a complete package for generating integer sequence in sqlserver or in t-sql, we can create a simple table-values function using the above concept. This will enable us to reuse this integer sequence generator anywhere in our database.
CREATE FUNCTION [dbo].[FuncIntegerSequence]
(
@count int,
@start_int int = 1
)
RETURNS TABLE
AS
RETURN
(
select A.sequence + @start_int-1 as sequence
from
(
select row_number() over (order by A.name) as sequence
from sys.all_objects A, sys.all_objects B
) A
where A.sequence <= @count
)
GO
This function returns a table with one column named sequence. This takes 2 parameter. The first one is the count of integer we want. The second one is the starting integer number from which our sequence should start. We can even use this to generate integer sequence starting from a negative number.
Sample execution:
select sequencer
from [dbo].[FuncIntegerSequence](100, 90)
select sequence
from [dbo].[FuncIntegerSequence](100, -200)
Table-Valued Function to Generate Date Sequence:
We can create a similar function to generate date sequence in t-sql. The following function takes to parameters. The first one is the starting date and the second one is the ending date. It returns a table with a single column named date_sequence that contains the consecutive dates.
CREATE FUNCTION [dbo].[FuncDateSequence]
(
@from_date datetime,
@to_date datetime
)
RETURNS TABLE
AS
RETURN
(
select A.sequence-1 + @from_date as date_sequence
from
(
select row_number() over (order by A.name) as sequence
from sys.all_objects A, sys.all_objects B
) A
where A.sequence <= DATEDIFF(DAY, @from_date, @to_date)
)
GO
Sample Execution:
select date_sequence
from [dbo].[FuncDateSequence]('2009-03-01', '2015-04-10')