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

I'm really thinking about switching away from Identity columns in SQL Server 2005 and going with some type of sequence generator (as far as I know there's nothing built in to 2005 to do this; may be coming in 2011?) to get unique id's for inserted rows.

Is there a model implementation or best practice for this? Will I encounter locking issues? What are the down sides compared to just using Identity columns?

Solution 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 do a sequence generator is to use UPDATE WITH OUTPUT on a sequences table. Here is 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 left out some details, but this is the general idea. However, there is a huge problem: any transaction requesting the next value on a sequence will lock that sequence until it commits, because it will place an update lock on the sequence value. This means that all transactions have to serialize after each other when inserting values and the performance degradation that results is unbearable in real production deployments.

I would much rather have you stick with the IDENTITY types. While not perfect, they are far better than what you can achieve on your own.

Solution 2:

The way that i used to solve this problem was a table 'Sequences' that stores all my sequences and a '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)  
);

The PK_Sequences is used just to be sure that there will never be sequences with the same name.

Sql Stored 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 some 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 get next value of a sequence,

execute nextval 'SEQ_Participant';  

Some c# code to get the next value from 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;
}