How to get the next value of the SQL Server sequence (this article was found on the network)

To create a Sequence object using sql:

IF EXISTS (SELECT * from sys.sequences WHERE name = N ' Testseq ')

DROP SEQUENCE Testseq;

GO

--Create a Sequence object

CREATE SEQUENCE testseq as tinyint

START with 1

INCREMENT by 1;

GO

--Create a table

CREATE TABLE TEST

(ID tinyint, Name varchar (150))

--generating serial numbers and inserting them into the table

INSERT into TEST

(Id,name)

VALUES

(NEXT VALUE for Testseq, ' Allen ')

INSERT into TEST

(Id,name)

VALUES

(NEXT VALUE for Testseq, ' Kevin ')

SELECT * from TEST

-The resulting sequence can be reused, and the following example starts again from 0 after the serial number exceeds 255.

CREATE SEQUENCE [dbo]. [Test1seq]

As [tinyint]

START with 0

INCREMENT by 5

CYCLE

SELECT

NEXT VALUE for [Test1seq]

GO 110

--Re-converts the initial value of the sequence into 5

ALTER SEQUENCE dbo. Testseq

RESTART with 5;

SELECT * from sys.sequences WHERE name = ' Testseq '

--shared sequence, two tables can use the same sequence object (the identity column is not possible)

CREATE SEQUENCE dbo. Seq as INT

START with 1

INCREMENT by 1

CREATE TABLE dbo. Examp1

(

Seq INT not NULL,

Name VARCHAR (not NULL)

);

CREATE TABLE dbo. Examp2

(

Seq INT not NULL,

Name VARCHAR (Notnull)

);

INSERT into dbo. EXAMP1 (Seq,name) VALUES (NEXT VALUE for dbo.) Seq, ' Tom ');

INSERT into dbo. EXAMP2 (Seq,name) VALUES (NEXT VALUE for dbo.) Seq, ' Jerry ');

SELECT * from EXAMP1

SELECT * from EXAMP2

--You can use the sequence object directly in the query (the identity column is not possible)

CREATE SEQUENCE Seqorder as tinyint

START WITH1

INCREMENT BY1

MINVALUE 1

NO MAXVALUE

CYCLE;

GO

SELECT ID, Name,

NEXT VALUE for Seqorder over (order by Name DESC) as [order]

From Test;

To improve performance, you can also use the cache option, which allows you to cache serial numbers to memory and reduce IO operations, and it is worth mentioning that the Sequence object can specify the maximum and minimum values, and the increment value can be negative.

How to get the next value of the SQL Server sequence (this article was found on the network)