SQL Server 2005 - using generated sequences instead of Identity columns?

Question

I really think about discarding Identity columns in SQL Server 2005 and comes with some sort of sequence generator (as far as I know that nothing was created for this in 2005, may appear in 2011?) Until I get a unique identifier for the inserted rows .

Is there a model implementation or best practice for this? Am I having trouble locking? What are the downsides to using Identity columns?

Answer 1

Yes, SQL 11 has SEQUENCE objects, see SQL Server v.Next (Denali): Using SEQUENCE .

Creating manual sequences is possible, but not recommended. The trick to creating a sequence generator is to use UPDATE WITH OUTPUT in the sequence table. Here is the pseudo code:

CREATE TABLE Sequences ( Name sysname not null primary key, Sequence bigint not null default 0); GO CREATE PROCEDURE sp_getSequence @name sysname, @value bigint output AS UPDATE Sequences SET Sequence = Sequence + 1 OUTPUT @value = INSERTED.Sequence WHERE Name = @name; GO 

I forgot some details, but this is a general idea. However, there is a huge problem: any transaction requesting the next value in the sequence will block this sequence until it is completed, since it will place the update lock on the sequence value. This means that all transactions must be sequentially serialized one after another when inserting values ​​and degrading performance, which is unbearable in real production deployments.

I would prefer you stick with IDENTITY types. Although not perfect, they are much better than you can achieve yourself.

Answer 2

What I used to solve this problem is the "Sequences" table, which stores all my sequences and the "nextval" stored procedure.

Sql table:

 CREATE TABLE Sequences ( name VARCHAR(30) NOT NULL, value BIGINT DEFAULT 0 NOT NULL, CONSTRAINT PK_Sequences PRIMARY KEY (name) ); 

PK_Sequences is used to ensure that there will never be sequences with the same name.

Stored Sql procedure:

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'nextVal') AND type in (N'P', N'PC')) DROP PROCEDURE nextVal; GO CREATE PROCEDURE nextval @name VARCHAR(30) AS BEGIN DECLARE @value BIGINT BEGIN TRANSACTION UPDATE Sequences SET @value=value=value + 1 WHERE name = @name; -- SELECT @value=value FROM Sequences WHERE name=@name COMMIT TRANSACTION SELECT @value AS nextval END; 

Insert a few sequences:

 INSERT INTO Sequences(name, value) VALUES ('SEQ_Workshop', 0); INSERT INTO Sequences(name, value) VALUES ('SEQ_Participant', 0); INSERT INTO Sequences(name, value) VALUES ('SEQ_Invoice', 0); 

Finally, we get the following value of the sequence,

 execute nextval 'SEQ_Participant'; 

Some C # code to get the following value from the Sequence table,

 public long getNextVal() { long nextval = -1; SqlConnection connection = new SqlConnection("your connection string"); try { // Connect and execute the select sql command. connection.Open(); SqlCommand command = new SqlCommand("nextval", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "SEQ_Participant"; nextval = Int64.Parse(command.ExecuteScalar().ToString()); command.Dispose(); } catch (Exception) { } finally { connection.Dispose(); } return nextval; }