About This Blog

A copy of my content originally hosted on SQLBlog.com

Tuesday, 19 October 2010

Sequence Tables

Sequence Tables

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 needs a more flexible scheme than is provided by the IDENTITY property. One alternative is to use a Sequence Table.