You are no doubt used to IDENTITY and how this works with a seed and increment value. SEQUENCE is structured in a very similar way, but with fewr limitations, giving it a welcome flexibility.
A SEQUENCE object is created at the database level but, unlike an IDENTITY property, it can be used across multiple tables. An IDENTITY value is generated when you insert a row into a table and it cannot be updated. You can retrieve the SEQUENCE value at any time and reset it without altering its previous value, and even set a minimum and maximum value. Let us look at a simple example:
CREATE SEQUENCE mySequence AS int START WITH 1 INCREMENT BY 1
As you can see, mySequence is now available for use by all tables in the database:
We have not used the SEQUENCE object yet, so the first value returned should be 1. Run the following statement to confirm this:
SELECT NEXT VALUE FOR mySequence AS [Next Value]
We can see that the SEQUENCE has not been used:
Next we will create a table so we can put SEQUENCE...