How to use sequence

What is sequence , we saw it in previous article
Now we will use it practically to understand how it works

First we will create a sequence




CREATE SEQUENCE test
     AS int
     START WITH 1
     INCREMENT BY 1
     MAXVALUE 5
     NO CYCLE
     CACHE;

Here I have created a sequence test , with no minvalue and maxvalue is 5.
So its minvalue will be minimum value supported by data type INT
Also this sequence will not restart as we have defined NOCYCLE

Now we will call this sequence


DECLARE
   @i int = 1;
WHILE @i <= 10
    BEGIN
        SELECT NEXT VALUE FOR test;
        SET @i+=1;
    END;



So sequence stopped after reaching value of 5

We can see this in master table

SELECT *
  FROM sys.sequences



We can see it has exhausted its value

Now we will restart it

ALTER SEQUENCE test
     CYCLE;


DECLARE
   @i int = 1;
WHILE @i <= 10
    BEGIN
        SELECT NEXT VALUE FOR test;
        SET @i+=1;
    END;




Here we can see that sequence started with lowest value supported INT datatype


But we want it to start with 1

alter sequence test  restart with 1  minvalue 1

DECLARE
   @i int = 1;
WHILE @i <= 10
    BEGIN
        SELECT NEXT VALUE FOR test;
        SET @i+=1;
    END;



Here we can see its restarted with 1 and minvalue is set to 1
so it repeated values 1 to 5.



No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts