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,
