Sequence Part 1:- New Feature in Denali

Sequences
Sequences are new feature in SQL Server Denali
Those who are aware with Oracle/Postgres are aware with sequence. However better late than never :-)
MS has added sequence in Deanli

This can help us to solve many problems we are facing with Identity and programmer will have more control on DB.Sequences are not bounded to tables and they are controlled by applications.

Lets see with Example for it

Definition from BOL



CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH ]
    [ INCREMENT BY ]
    [ { MINVALUE [ ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ ] } | { NO CACHE } ]
    [ ; ]
   

    A sequence can be defined as any Intger Type
    Bigint is a default option   
   
 START WITH
    The first value from where the sequence starts. The value must be constant

 INCREMENT BY
    The increment value for the sequence. The value must be constant
 MINVALUE
    Minvalue for the sequence
 MAXVALUE
    Maxvalue for the sequence

CYCLE
    The sequence should restart when it reaches maxvalue
    Cycle will restart from minvalue/maxvalue not from start value
   

   
Lets see with an Example for this

CREATE SCHEMA test

Creating a sequence

CREATE sequence test.seq

AS int

start WITH 1

increment BY 1

create table testtable

(id int,

name varchar(10))

INSERT INTO testtable

VALUES

(next value for test.seq, 'a') ,

(next value FOR test.seq, 'b') ,

(next value FOR test.seq, 'c')

SELECT *

FROM   testtable 

Now we want to set the sequnce number to 10

Altering a sequence

ALTER sequence  test.seq

restart WITH 10

INSERT INTO testtable

VALUES

(next value for test.seq, 'd') ,

(next value FOR test.seq, 'e') ,

(next value FOR test.seq, 'f')

SELECT *

FROM   testtable

Use of Minvalue, Maxvalue, Cycle  


lets cycle the sequence  with range of values

CREATE sequence test.seqnew

AS int

start WITH 1

increment BY 1

minvalue 1

maxvalue 3

cycle

TRUNCATE TABLE testtable

INSERT INTO testtable

VALUES

(next value for test.seqnew, 'a') ,

(next value FOR test.seqnew, 'b') ,

(next value FOR test.seqnew, 'c') ,

(next value for test.seqnew, 'd') ,

(next value FOR test.seqnew, 'e') ,

(next value FOR test.seqnew, 'f')

   

SELECT *

FROM   testtable

    

    

Here you can see the seuqence has range from1 to 3 and after than it restarts with 1.