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.

1 comment:

Post a Comment

Amazon Contextual Product Ads

Popular Posts