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

by

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.

More Related Contents:

  1. What is User define function and how to execute that? [closed]
  2. Retrieving last record in each group from database – SQL Server 2005/2008
  3. Base64 encoding in SQL Server 2005 T-SQL
  4. Integer division in sql server
  5. Nested stored procedures containing TRY CATCH ROLLBACK pattern?
  6. How to change identity column values programmatically?
  7. get DATEDIFF excluding weekends using sql server
  8. How to upsert (update or insert) in SQL Server 2005
  9. SQL query to find Missing sequence numbers
  10. How to use alias column name in where clause in SQL Server
  11. Convert .NET Ticks to SQL Server DateTime
  12. Executing dynamic SQL in a SQLServer 2005 function
  13. Advantages of SQL Server 2008 over SQL Server 2005? [closed]
  14. Isoweek in SQL Server 2005
  15. How to delete all rows from all tables in a SQL Server database?
  16. Convert SQL server datetime fields to compare date parts only, with indexed lookups
  17. How to update data in one table from corresponding data in another table in SQL Server 2005
  18. Foreign key relationship with composite primary keys in SQL Server 2005
  19. Unique Key Violation in SQL Server – Is it safe to assume Error 2627?
  20. SQL server identity column values start at 0 instead of 1
  21. What good are SQL Server schemas?
  22. What is difference between datetime and timestamp
  23. How do I check if a Sql server string is null or empty
  24. Problem with Renaming a Column in SQL Server
  25. SQL Server 2005 Computed Column Is Persisted
  26. SQL Server: How to permission schemas?
  27. Aggregate SQL Function to grab only the first from each group
  28. SQL Server: How to tell if a database is a system database?
  29. Multiple Select Statements using SQL Server 2005 “WITH” Statement
  30. How do you store a picture in an image column?

Leave a Comment