Creating a SEQUENCE object is simple.
CREATE SEQUENCE dbo.MySeq AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE CACHE 20 ;
Now using it:
SELECT NEXT VALUE FOR dbo.MySeq GO 4
The result would be something like this:
(no column name)
1
2
3
4
If we execute again the previous query, the result will be numbers from 5 to 8, and so on. However, if we want to reinitialise the sequence, it can be done as follows:
ALTER SEQUENCE dbo.MySeq RESTART
Now using SEQUENCE as an alternative of ROW_NUMBER:
select next value for dbo.MySeq as [nro], Employees.FirstName from Employees
And also in this way:
select next value for dbo.MySeq over (order by customers.CustomerID ) as [nro], customers.CustomerID, Customers.ContactName from Customers
Here are some resources to check out more information about SEQUENCE:
- Creating and Using Sequence Numbers.
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx - sp_sequence_get_range (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ff878352(v=SQL.110).aspx - SQL Server v.Next (Denali): Using SEQUENCE http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx
That is all for now, let me know any remark you may have. Thanks for reading.