Applied SQL…

In my previous article on Sequence we went through how we can create and implement Sequence.In this short article I will demonstrate how we can achieve the following:

1) How to get the current value of the Sequence
2) How to get the next Sequence Value
3) Restarting the Sequence Value
4) Dropping a Sequence object

1) current value of the Sequence
To find the current value of a Sequence we need to query the system view sys.sequences.

USE AdventureWorks2012
go
SELECT Current_Value FROM sys.sequences
WHERE name= 'MySequence1' -- specify the name of the sequence
GO

2) get the next Sequence Value:

use Adventureworks
go
SELECT (NEXT VALUE FOR MySequence1)
AS NextSequenceValue

3) Restarting the Sequence Value:

USE [AdventureWorks2012]
GO
ALTER SEQUENCE [dbo].[MySequence1]
RESTART  WITH 100001
INCREMENT BY 10
NO MINVALUE
MAXVALUE 200000

4) Dropping a Sequence object:

use Adventureworks2012
go
DROP SEQUENCE MySequence1;
go

Related articles

• Life is a SEQUENCE of events (appliedsql.wordpress.com)