Create SEQUENCE in SQL Server 2012

Create Sequence in SQL Server 2012.

          SQL Server 2012   Sequence object gives sequences numbers of User choice. i.e
It has start value property, increment by , Minimum ,Maximum Value Properties.
Its not associated with table column, But associated with schema.


In addition user can specify datatype(tinyint,smallint,int,bigint,decimal and numeric with scale of 0, or user defined type.)

By default: bigint

Syntax:

             Create Sequence <Sequence name> [start with <value> increment by <value> MinValue <value> MaxValue <value>];

Create sequence with datatype Syntax

create sequence <sequence_name> as tinyint  start with <value>;

Note: Sequence Range depends on Datatype used.

for ex: for tinint MinValue is 0 Max Value is 255.
           for int MIN VALUE -2,147,483,648  MAX VALUE. 2,147,483,647
           etc.,


For ex:  Create Sequence start value as 1.

 Create Sequence sequence1  start with 1;

select    next value for sequence1   -- outputs 1 ,next execution 2 etc.,

For ex:  Create Sequence with start value , Minimum and Maximum values.

1.  Create Sequence sequence2  start with 1  MinValue 1 MaxValue 100;



For ex:  Create Sequence start value , Increment , Minimum and Maximum values.

1.  Create Sequence sequence3  start with 1 increment by 1 MinValue 1 MaxValue 100;

For ex:  Create Sequence start value , Increment by 2, Minimum and Maximum values.

1.  Create Sequence sequence4  start with 1 increment by 2 MinValue 1 MaxValue 100;


For ex:  Create Sequence start value as -1 , Increment by 2, Minimum and Maximum values.

Create Sequence sequence4  start with -1 increment by -2 MinValue -100 ;
select next value for sequence4  //outputs from -1, next -3 ,-5 etc.,


SEQUENCE VALUE IN INSERT STATEMENT

INSERT INTO SPORTS(ID,NAME,COUNTRY) VALUES(NEXT VALUE FOR SEQUENCE1,
'HAND FOODBALL','US');

ASSUMING ID COLUMN IS NOT IDENTITY COLUMN if so set IDENTITY_INSERT IS on.


SEQUENCE VALUE IN UPDATE STATEMENT

UPDATE SPORTS SET ID=NEXT VALUE FOR SEQUENCE4 WHERE ID=23;


RESET SEQUENCE VALUE

Create sequence seq1 start with 1;
select next value for seq1 ;   outputs  1, next time 2,3

If u want to reset the Sequence value   use alter sequence

alter sequence seq1 restart with 1

select next value for seq1; again outputs 1,2,3 etc.,

CHANGE MINIMUM VALUE IN SEQUENCE

alter sequence seq1 MINVALUE 5;

user may be error message "
The current value '1' for alter sequence seq1 Minvalue 5sequence object 'seq1' must be between the minimum and maximum value of the sequence object.
"
so start value should be between minimum and maximum value.

alter sequence seq1 RESTART with 6 MINVALUE 5;

select next value for seq1 outputs  6,7,8 etc.,

CHANGE MAXIMUM VALUE IN SEQUENCE.

alter sequence seq1 MAXVALUE 10

 Note:Maximum value should be greater than  Minimum value.


DROP THE SEQUENCE

Drop Sequence <sequenceName>

Get all Sequences in the Database

select * from sys.sequences.

Get Current Value of the Sequence.

select current_value from sys.sequences where name ='<sequence_name>';

Get Maximum Minimum Values of the Sequence

select minimum_value,maximum_value from sys.sequences where name ='<sequence_name>';


Tags: Create SEQUENCE in SQL Server 2012,Get Maximum Value of the Sequence,Get Minimum Value of the Sequence,Get all sequences in the database,Drop the Sequence,Change Maximum value of the Sequence,Create Sequence,Create sequence with datatype,