sql server 2012 sequence

Sequence Object curbs the limitation we discussed on the previous blog. Because it is independent object it doesn’t have to be associated with a particular column and it could be used multiple times in a given table. We could be able to generate keys that are unique globally which I think is great in a distributed database model. Sequence object feature is not available on versions before SQL Server 2012.
To create sequence object use script create sequence [schema].sequencename [as] [data type];

The data type is optional if it is not specified sql server will take is bigint. And any 0 scale numeric data type can be used such as decimal(4,0). Sequence object has properties such as increment by, minvalue, maxvalue, cycle / no cycle, start with (the default value of start with if it is not specified is the minimum value of the data type used)
To see how we could work with Sequence object let us create the following table

if exists(select 1 from INFORMATION_SCHEMA.TABLES 
		  where TABLE_NAME = 'SalesOrders')
drop table dbo.SalesOrders;		
CREATE TABLE dbo.SalesOrders(
	SalesID int not null,
	OrderDate datetime NULL,
	Region nvarchar(255) NULL,
	Rep nvarchar(255) NULL,
	Item nvarchar(255) NULL,
	Units int NULL,
	[Unit Cost] money NULL,
	Total money NULL
);
GO

And create a Sequence object like this

create sequence dbo.salesSequence as bigint
	start with 1
	increment by 1
	no cycle
	no cache
go

To simply select what the next value of the sequence object is we could use the script below this is something we can’t do while using identity

select next value for dbo. salesSequence;

To insert value to our previous table and use sequence object we can use this way

insert into dbo.SalesOrders 
	SELECT Next value for dbo. salesSequence
          ,OrderDate
		  ,Region
		  ,Rep
		  ,Item
		  ,Units
		  ,cast([Unit Cost] as money)
		  ,cast(Total as money)
	FROM dbo.tSalesOrders;
go
 
-------------select SalesOrder
select * from dbo.SalesOrders;

As you can see here the sequence is created and inserted just like identity.
A few things to notice here is NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.
We could sequence object in default constraint of a before it is used.

-------------create table with default constraint
if exists(select 1 from INFORMATION_SCHEMA.TABLES 
		  where TABLE_NAME = 'SalesOrders')
drop table dbo.SalesOrders;		
CREATE TABLE dbo.SalesOrders(
	SalesID int not null
	constraint SalesOrders_SalesID
		default(next value for dbo.salesSequence) ,
	OrderDate datetime NULL,
	Region nvarchar(255) NULL,
	Rep nvarchar(255) NULL,
	Item nvarchar(255) NULL,
	Units int NULL,
	[Unit Cost] money NULL,
	Total money NULL
);
go

Now if we could insert and see select the table same way as we did

-------------insert
insert into dbo.SalesOrders 
	SELECT Next value for dbo. salesSequence
          ,OrderDate
		  ,Region
		  ,Rep
		  ,Item
		  ,Units
		  ,cast([Unit Cost] as money)
		  ,cast(Total as money)
	FROM dbo.tSalesOrders;
go
-------------select salesOrder
select * from dbo.SalesOrders;

This time we have the sequence object in the default constraint indeed added identity values but the first value started from 44 because we added 43 records in the previous insert statement. If we need to start the sequence object from one in the next insertion we should use

-------------reset sequence object
alter sequence dbo.salesSequence restart with 1 ;

Sequence object could be used as a partition functions with OVER clause and Order BY clause. For example in our table if we want to assign identity based on the order of Sales Reps we could use

-------------insert over
insert into dbo.SalesOrders 
	SELECT Next value for dbo.salesSequence over( order by Rep)
          ,OrderDate
		  ,Region
		  ,Rep
		  ,Item
		  ,Units
		  ,cast([Unit Cost] as money)
		  ,cast(Total as money)
	FROM dbo.tSalesOrders;
go
-------------select order by Rep
select * from dbo.SalesOrders order by Rep;

We could use sequence for more than one column in a given table unlike identity property.

-------------insert on multiple columns
insert into dbo.SalesOrders 
	SELECT Next value for dbo.salesSequence Sequence1
          ,OrderDate
		  ,Region
		  ,Rep
		  ,Item
		  ,Next value for dbo.salesSequence Sequence2		 
		  ,cast([Unit Cost] as money)
		  ,cast(Total as money)
	FROM dbo.tSalesOrders;
go
-------------select order by Rep
select * from dbo.SalesOrders order by Rep;

We could get the current value of a sequence object using

-------------select current sequence value
SELECT cast(current_value as int) 
FROM sys.sequences WHERE name = 'salesSequence'