Séquences en SQL Server

Les séquences (compteurs) sont introduits en 2012. Par contre, pour les anciennes versions il vous faut les implémenter.

Implémentation 1

Avantages: une tables pour toutes les séquences
Désavantages: pour éviter les verrouillages lors d'insertions intensives depuis les connexions simultanées il faut l’appeler hors de la transaction

CREATE TABLE counters (
  name sysname,
  value int,
  CONSTRAINT PK_COUNTERS PRIMARY KEY (name)
)
GO
 
CREATE PROCEDURE GetNextValue(@counter sysname, @value int out)
AS BEGIN
  UPDATE Counters
    SET @value = value = value + 1
    WHERE counter = @counter
END

Implémentation 1 - ANSI SQL

CREATE TABLE counters (
  name varchar(128),
  value int,
  CONSTRAINT PK_COUNTERS PRIMARY KEY (name)
)

Pour récupérer la valeur

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
 
UPDATE counters
SET value = value + 1
WHERE counter = 'table name'
 
SELECT value 
FROM counters
WHERE counter = 'table name'
 
COMMIT

Implémentation 2

Avantages: pas de verrouillages dans la transaction
Désavantages: il faut créer une table pour chaque séquence

CREATE TABLE counter_myname (
  value int identity(1, 1),
  foo bit,
  CONSTRAINT PK_COUNTERS PRIMARY KEY (value)
)
GO
 
CREATE PROCEDURE GetNextMyNameValue(@value int out)
AS BEGIN
  SET NOCOUNT ON;
  IF @@trancount > 0
    SAVE TRANSACTION tnx_GetNextMyNameValue
  ELSE
    BEGIN TRANSACTION tnx_GetNextMyNameValue
  INSERT INTO counter_myname (foo) VALUES (0);
  SET @value = @@IDENTITY
  ROLLBACK TRANSACTION tnx_GetNextMyNameValue
END

Test

CREATE PROCEDURE Sequence_Test
AS 
BEGIN
  DECLARE @id int
  CREATE TABLE #t(id int)
  BEGIN TRANSACTION
  EXEC GetNextMyNameValue @value = @id OUTPUT
  INSERT INTO #t VALUES(@id)
  EXEC GetNextMyNameValue @value = @id OUTPUT
  INSERT INTO #t VALUES(@id)
  COMMIT
  SELECT * FROM #t
 
  EXEC GetNextMyNameValue @value = @id OUTPUT
  INSERT INTO #t VALUES(@id)
  SELECT * FROM #t
END
GO
 
-- Lancez en même temps dans plusieurs connexions
EXEC Sequence_Test
SELECT * FROM counter_myname