Getting next value

Hi folks. I'd like to share with you some technique to get a next value from the table means to create your own sequence mechanism.
As we all know that an Identity property may have gaps, so use the below script to retrieve a next value. We create a table with a one row and a only one column which holding the last used sequence value.


CREATE TABLE seq(col int not null);
INSERT INTO seq values(0);
go
SELECT * FROM seq

CREATE PROC spget_nextseq @next_val AS INT OUTPUT
AS
UPDATE seq SET @next_val= col = col + 1;
go

-- usage
DECLARE @i as int;
EXEC spget_nextseq @i output;
SELECT @i;

Note, in OLTP application where many connections run this script you may end up with deadlocks. One way to pevent it is using lock hint called TABLOCK

ALTER PROC spget_nextseq @next_val AS INT OUTPUT
AS
UPDATE seq SET @next_val= col = col + 1 FROM seq WITH (TABLOCK);
go