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.