About This Blog

A copy of my content from SQLBlog.com and SQLPerformance.com, plus occasional new content.

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.