It is frequently useful to generate sequences of values within SQL Server, perhaps for use as surrogate keys. Using the IDENTITY property on a column is the easiest way to automatically generate such sequences:
CREATE TABLE dbo.SomeTable
(
row_id INTEGER IDENTITY PRIMARY KEY,
data SQL_VARIANT NOT NULL,
);
Sometimes though, the database designer finds that she needs a more flexible scheme than is provided by the IDENTITY property. One alternative is to use a Sequence Table.
What is a Sequence Table?
A Sequence Table is a regular table that stores the next available value for one or more sequences – somewhat like a manual identity value in many respects. The definition of a Sequence Table might look like this:
Read More...