Using a trigger to simulate a second identity column in SQL Server 2005.

Question

I have various reasons for needing to implement, in addition to the identity column PK, a second, concurrency safe, auto-incrementing column in a SQL Server 2005 database. Being able to have more than one identity column would be ideal, but I'm looking at using a trigger to simulate this as close as possible to the metal.

I believe I have to use a serializable isolation level transaction in the trigger. Do I go about this like Ii would use such a transaction in a normal SQL query?

It is a non-negotiable requirement that the business meaning of the second incrementing column remain separated from the behind the scenes meaning of the first, PK, incrementing column.

To put things as simply as I can, if I create JobCards '0001', '0002', and '0003', then delete JobCards '0002' and '0003', the next Jobcard I create must have ID '0002', not '0004'.

Answer 1

Just an idea, if you have 2 "identity" columns, then surely they would be 'in synch' - if not exactly the same value, then would differ by a constant value. If so, then why not add the "second identity" column as a computed column, which offsets the primary identity? Or is my logic flawed here?

Edit : As per Martin's comment, note that your calc might need to be N * id + C, where N is the Increment and C the offset / delta - excuse my rusty maths.

Edit : OP has restated the requirement (noting that identity columns in SQL do not reclaim used ID's once deleted) so this answer is no longer applicable.

Answer 2

A solution to this issue from "Inside Microsoft SQL Server 2008: T-SQL Querying" is to create another table with a single row that holds the current max value.

CREATE TABLE dbo.Sequence(
 val int 
 )

Then to allocate a range of sufficient size for your insert

CREATE PROC dbo.GetSequence
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequence 
SET @val = val = val + @n;

SET @val = @val - @n + 1; 

This will block other concurrent attempts to increment the sequence until the first transaction commits.

For a non blocking solution that doesn't handle multi row inserts see my answer here.

Answer 3

if you never delete from the table, you could create a view with a materialized column that uses ROW_NUMBER().

ALSO, a SQL Server identity can get out of sync with a user generated one, depending on the use of rollback.

Answer 4

I would disallow all the deletes from this table altogether. Instead of deleting, I would mark rows as available or inactive. Instead of inserting, I would first search if there are inactive rows, and reuse the one with the smallest ID if they exist. I would insert only if there are no available rows already in the table.

Of course, I would serialize all inserts and deletes with sp_getapplock.

You can use a trigger to disallow all deletes, it is simpler than filling gaps.

Answer 5

Just use a regular identity and reseed on deletes.

create table reseedtest (
   a int identity(1,1) not null,
   name varchar(100)
)

insert reseedtest values('erik'),('john'),('selina')
select * from reseedtest

go
CREATE TRIGGER TR_reseedtest_D ON reseedtest FOR DELETE
AS
BEGIN TRAN
DECLARE @a int
SET @a = (SELECT TOP 1 a FROM reseedtest WITH (TABLOCKX, HOLDLOCK))
--anyone know another way to lock a table besides doing something to it?
DBCC CHECKIDENT(reseedtest, reseed, 0)
DBCC CHECKIDENT(reseedtest, reseed)
COMMIT TRAN
GO

delete reseedtest where a >= 2
insert reseedtest values('katarina'),('david')
select * from reseedtest

drop table reseedtest

This won't work if you are deleting from the "middle of the stack" as it were, but it works fine for deletes from the incrementing end.

Reseeding once to 0 then again is just a trick to avoid having to calculate the correct reseed value.