SQL Server Sequences

In SQL Server, you can create an autonumber field by using sequences. A sequence is an object in SQL Server (Transact-SQL) that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Creating sequences.

CREATE SEQUENCE contacts_seq
  AS BIGINT
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 99999
  NO CYCLE
  CACHE 10;

CREATE SEQUENCE contacts_seq
  START WITH 1
  INCREMENT BY 1;

Using sequences.

INSERT INTO contacts
(contact_id, last_name)
VALUES
(NEXT VALUE FOR contacts_seq, 'Smith');

SELECT NEXT VALUE FOR contacts_seq;

Drop Sequences

DROP SEQUENCE contacts_seq;

Properties of Sequences

SELECT *
FROM sys.sequences
WHERE name = 'sequence_name';

SELECT *
FROM sys.sequences
WHERE name = 'contacts_seq';

Example 1:

CREATE SEQUENCE [dbo].[tbMyTable01SequenceGen] 
 AS [bigint]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 CACHE 
GO

SELECT @biMyTable01Id = NEXT VALUE FOR tbMyTable01SequenceGen

Sources:

https://www.techonthenet.com/sql_server/sequences.php