How would you implement sequences in Microsoft SQL Server?

Question

Does anyone have a good way of implementing something like a sequence in SQL server?

Sometimes you just don't want to use a GUID, besides the fact that they are ugly as heck. Maybe the sequence you want isn't numeric? Besides, inserting a row and then asking the DB what the number just seems so hackish.

Answer 1

This seems to address your question directly: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

(if it doesn't -- I must not be understanding some subtlety of your use case. Please comment)

Answer 2

An Identity column is roughly analogous to a sequence.

Answer 3

You could just use plain old tables and use them as sequences. That means your inserts would always be:
BEGIN TRANSACTION
SELECT number from plain old table..
UPDATE plain old table, set the number to be the next number
INSERT your row
COMMIT

But don't do this. The locking would be bad...

I started on SQL Server and to me, the Oracle "sequence" scheme looked like a hack. I guess you are coming from the other direction and to you, and scope_identity() looks like a hack.

Get over it. When in Rome, do as the Romans do.

Answer 4

I totally agree and did this last year on a project.

I just created a table with the name of the sequence, current value, & increment amount.

Then I created a 2 procs to add & delete them. And 2 functions to get next, & get current.

Answer 5

If you want to insert data with a sequential key, but you don't want to have to query the database again to get the just-inserted key, I think your only two choices are:

1. Perform the insert through a stored procedure which returns the newly-inserted key value
2. Implement the sequence client-side (so that you know the new key before you insert)

If I'm doing client-side key generation, I love GUIDs. I think they're beautiful as heck.

row["ID"] = Guid.NewGuid();

That line should be laying on the hood of a sportscar somewhere.

Answer 6

It's part of the Relational Gospel that tuples are inherently unordered, and Set Logic requires that they be so. (Yes, that's by rote.)

In my experience, it'w always been just as easy to sequence the results in another abstraction layer (e.g. a grid in the UI). Trying to do this in the database is inevitably fraught with Unintended Consequences. The DBMS doesn't need (will trip over) sequencing, so interpose it at the layer that does. All else is problematic coupling.

Answer 7

sequences as implemented by oracle require a call to the database before the insert. identities as implemented by sqlserver require a call to the database after the insert.

one is no more hackish than the other. the net effect is the same - a reliance/dependency on the data store to provide unique artificial key values and (in most cases) two calls to the store.

i'm assuming that your relational model is based on artificial keys, and in this context, i'll offer the following observation:

we should never seek to imbue artificial keys with meaning; their only purpose should be to link related records.

what is your need related to ordering data? can it be handled in the view (presentation) or is it a true attribute of your data which must be persisted?

Answer 8

If you are using SQL Server 2005 you have the option of using Row_Number