What is Reseeding an identity column

Occasionally, you’ll need to reset or skip identity values for the column. To do this, you’ll be “reseeding” the column using the DBCC CHECKIDENT function. You can use this to skip many values, or to reset the next identity value to 1 after you’ve deleted all of the rows in the table. For full details using DBCC CHECKIDENT, see the Transact-SQL reference documentation.

SEQUENCE

In Transact-SQL, you can use a sequence object to define new sequential values independently of a specific table. A sequence object is created using the CREATE SEQUENCE statement, optionally supplying the data type (must be an integer type or decimal or numeric with a scale of 0), the starting value, an increment value, a maximum value, and other options related to performance.

SQLCopy

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

To retrieve the next available value from a sequence, use the NEXT VALUE FOR construct, like this:

SQLCopy

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);