Paul White NZ (7/15/2010)
mike 57299 (7/14/2010)
Paul,Do I need to do an explicit Begin Trans / Commit to make sure there is no duplicate sequence #'s?
Mike
Hey Mike,
No - and that's the beauty of it: it's an atomic operation (single UPDATE statement) which occurs within its own implicit transaction, as normal.
Paul
It's very similar to the sequence generator I use in my DB, but consider that this solution can lead to very extensive row locks if run inside transactions.
Example:
Open a new query in SSMS and run:
BEGIN TRAN
EXEC dbo.usp_GenPK N'dbo.MyTable', @NewID OUTPUT;
WAITFOR DELAY '00:02';
COMMIT
Open a new query and run the same code. This second query waits for the first transaction to end.
If you don't mind gaps in the sequence, use a CLR procedure that implements autonomous transactions (not enlisted in the context connection's transaction) to generate the new id. Unfortunately, the permission to open a non-context connection in a CLR procedure must be granted marking the assembly as "external" or "unsafe".
I'm writing an article on this subject and I hope I get it finished soon.
-- Gianluca Sartori