SQL SERVER Identity faire des trous dans les autoincrementés

Voici comment faire un trou dans un compteur auto incrémenté SQL SERVER.

Bon cela peut être utile même si s'est à utiliser avec parcimonie.

Un besoin est d'insérer tout de suite la dernière valeur possible d'une liste qui ne grossira pas au delà d'une valeur connue. Cela permet d'avoir le dernier Id supérieur à tous les autres insérés plus tard.

Ci dessous l'exemple complet :

--- création de la table

CREATE TABLE [dbo].[fiche](

       [fich_id] [int] IDENTITY(1,1) NOT NULL,

       [fich_nom] [varchar](50) NOT NULL,

       [fich_code] [varchar](50) NOT NULL,

       [fich_lang] [varchar](10) NOT NULL,

       [fich_type] [tinyint] NOT NULL,

 CONSTRAINT [PK_FICHE] PRIMARY KEY CLUSTERED ([fich_id] ASC)

)

--- remplissage

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T1', 'C1', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T2', 'C2', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T3', 'C3', 'fr-FR', 3)

--- vérification

SELECT * From fiche

fich_id fich_nom fich_code fich_lang fich_type

1 T1 C1 fr-FR 3

2 T2 C2 fr-FR 3

3 T3 C3 fr-FR 3

--- on crée le trou

IF (NOT EXISTS(SELECT 1 FROM [dbo].[fiche] WHERE fich_id = 9))

BEGIN

  DECLARE @fichId INT

  SELECT @fichId = ISNULL(MAX(fich_id), 0) FROM [dbo].[fiche] WHERE fich_id <> 9

  SET IDENTITY_INSERT [dbo].[fiche] ON;

  INSERT INTO fiche(fich_id, fich_nom, fich_code, fich_lang, fich_type) VALUES (9, 'T9', 'C9', 'fr-FR', 3)

  SET IDENTITY_INSERT [dbo].[fiche] OFF;

  DBCC CHECKIDENT ([dbo.fiche], RESEED , @fichId);

  --- on le fait une seconde fois par sécurité

  DBCC CHECKIDENT ([dbo.fiche], RESEED , @fichId);

END

--- vérification

SELECT * From fiche

fich_id fich_nom fich_code fich_lang fich_type

1 T1 C1 fr-FR 3

2 T2 C2 fr-FR 3

3 T3 C3 fr-FR 3

9 T9 C9 fr-FR 3

--- Vérification : on peut insérer jusqu'au dernier Id

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T4', 'C4', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T5', 'C5', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T6', 'C6', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T7', 'C7', 'fr-FR', 3)

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T8', 'C8', 'fr-FR', 3)

--- vérification

SELECT * From fiche

fich_id fich_nom fich_code fich_lang fich_type

1 T1 C1 fr-FR 3

2 T2 C2 fr-FR 3

3 T3 C3 fr-FR 3

4 T4 C4 fr-FR 3

5 T5 C5 fr-FR 3

6 T6 C6 fr-FR 3

7 T7 C7 fr-FR 3

8 T8 C8 fr-FR 3

9 T9 C9 fr-FR 3

--- la ça plante !!

INSERT INTO fiche( fich_nom, fich_code, fich_lang, fich_type) VALUES ('T10', 'C10', 'fr-FR', 3)

Msg 2627, Niveau 14, État 1, Ligne 1

Violation de la contrainte PRIMARY KEY 'PK_FICHE'. Impossible d'insérer une clé en double dans l'objet 'dbo.fiche'.

L'instruction a été arrêtée.