Sequences are a really cool feature we find now in SQL Server. They were available in other SQL engines for some time, and have been in SQL Server for a few releases now.
A sequence is a user definable SQL Server object used to generate a sequential number every time it is called. We are used to that capability in SQL with things like AutoNumber, or Identity columns, which the data engine manages,returning the next number in sequence each time a record is inserted into a table having one of these kinds of columns..
A sequence operates in much the same way, except you have to manually tell the sequence object to generate the next value, and then use the result yourself. It is not done as a result of inserting a record into a table.
So, why would you want to use a Sequence in your database? There are many times you want to have a sequential number generated guaranteed to be unique across all processes. A sequence may be used for processes outside of SQL Server, and take advantage of the multi-threading capabilities built into the SQL Server Sequence. The sequence is guaranteed to always return a unique value each time it’s called, and handles all of the multi-threading concerns.
Perhaps you want to have a sequential value that is guaranteed unique even if it is used in more than one table. In this case, the sequence object can serve that purpose as well. A call to the sequence each time a new value is required returns the next value, and you can insert/update any database object using that value.
Take a look at the sequence capability found in SQL. It is one of those features that is so simple that it is easy to overlook. The Sequence is on of those features that has a lot of potential, once you start thinking about it.
Cheers,
Ben