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);
SELECT * FROM seq
CREATE PROC spget_nextseq @next_val AS INT OUTPUT
UPDATE seq SET @next_val= col = col + 1;
DECLARE @i as int;
EXEC spget_nextseq @i output;
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
UPDATE seq SET @next_val= col = col + 1 FROM seq WITH (TABLOCK);