SQL Server : Recupérer l'auto incrémenté (1 de plus !)

 Pour récupérer la valeur d'une colonne auto incrémenté dans SQL Server on dispose de 3 méthodes : 

• SCOPE_IDENTITY()
• @@IDENTITY
• IDENT_CURRENT('Table')

Pour trouver quoi utiliser et quand, je vous laisse voir les documentation associées.

Pour ma part, j'ai pris l'habitude d'utiliser SCOPE_IDENTITY(), mais récemment, je me suis retrouvé bloqué par un INSERT dans une vue avec un trigger INSTEAD OF qui fait que le contexte d'insertion est différent du context d'appel et donc qui fait que  SCOPE_IDENTITY() renvoie NULL ;-(

Du coup pour ne plus se faire avoir (nouvelle grande décision), maintenant j'utilise l'instruction ci dessous plus longue mais marche dans plus de cas :

DECLARE @id INT;
SELECT @id = COALESCE(SCOPE_IDENTITY(), @@Identity);

Bien sûr il faut remettre cette astuce dans le seul contexte ou l'on ne veut insérer qu'une seule ligne à la fois.
Il faut utiliser une clause OUTPUT si plusieurs lignes vont être insérées en une seule opération.

Enjoy !

SQL - Surveiller un compteur d'identité

Sur les tables SQL Server mettre un compteur d'identité comme clé primaire, c'est bien. (Ça résous pas mal de problématiques).
Mais quand la base a une forte (très forte) activité et que l'on utilise des colonnes de type INT pour ces compteurs, ben des fois on arrive au bout du compteur !!!
A ce moment la c'est trop tard votre application va subir des bugs à répétition jusqu’à ce que vous solutionniez ce problème. Autant dire qu'il faut réagir vite et sous la pression. C'est pas bon.

Pour éviter cela je propose de surveiller ces compteurs et de faire un reporting qui vous indique quand agir par anticipation.

Voici ci dessous une procédure qui stocke dans une table dbo.compteur_identite la valeur de l'auto-incrémenté et combien de lignes il reste à insérer sereinement.
En dessous de 1 Million vous avez un warning.

--- Vider la table si elle existe déjà
IF (EXISTS (SELECT 1 FROM sys.schemas s 
                     INNER JOIN sys.tables t ON t.schema_id = s.schema_id 
                     WHERE s.name = 'dbo' 
                       AND t.name = 'compteur_identite'))
BEGIN
 DROP TABLE dbo.compteur_identite
END 

--- recréer la table de sortie
CREATE TABLE dbo.compteur_identite ( [schema] VARCHAR(10) NOT NULL
, [table] VARCHAR(100) NOT NULL, [index_pk] VARCHAR(100) NOT NULL
, [column] VARCHAR(100) NOT NULL, [type] VARCHAR(25) NOT NULL
, [seed] BIGINT NULL, [increment] BIGINT  NULL
, [max_id] NUMERIC(38,0)  NULL, [current] NUMERIC(38,0) NULL
, [nombre de lignes restant] NUMERIC(38,0) NULL
, [critique] VARCHAR(10) NULL)


--- curseur pour parcourir toutes les tables de la base de données
DECLARE @schema VARCHAR(5);
DECLARE @table VARCHAR(100);
DECLARE curseur CURSOR FOR SELECT s.name, o.name 
                           FROM sys.schemas s 
                           INNER JOIN sys.objects o ON o.schema_id = s.schema_id 
                           WHERE s.name <> 'sys' 
                             AND o.name NOT IN ('MASTERKEY', 'spt_values')  --- ici exclure ce qui n'est pas nécessaire

OPEN curseur
FETCH NEXT FROM curseur INTO  @schema, @table
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
  PRINT @schema + '.' + @table;
  --- insertion dans la table de sortie
  INSERT INTO dbo.compteur_identite ( [schema]
    , [table], [index_pk]
    , [column], [type]
    , [seed], [increment]
    , [max_id], [current]
    , [nombre de lignes restant]
    , [critique])
  SELECT s.name AS [schema]
    , o.name AS [table], i.name AS [index_pk]
    , c.name AS [column], t.name AS [type]
    , IDENT_SEED(@schema + '.' + @table) AS [seed], IDENT_INCR(@schema + '.' + @table) AS [increment]
    , CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) AS [max_id], IDENT_CURRENT( @schema + '.' + @table) AS [current]
    , CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) AS [nombre de lignes restant]
    , CASE WHEN CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table )  < 1000000 THEN 'Warning' ELSE 'Non' END AS [critique]
  FROM sys.indexes i 
  INNER JOIN sys.objects o ON i.object_id = o.object_id 
  INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
  INNER JOIN sys.index_columns cl ON cl.object_id = o.object_id AND i.index_id = cl.index_id
  INNER JOIN sys.columns c ON c.object_id = o.object_id AND cl.column_id = c.column_id AND c.is_identity = 1
  INNER JOIN sys.systypes t ON c.user_type_id = t.xusertype AND t.collation IS NULL AND t.length > 0 AND xprec <> 0
  WHERE i.type = 1  --- pk
    AND s.name = @schema 
    AND o.name = @table
  ;
  FETCH NEXT FROM curseur INTO  @schema, @table;
END
CLOSE curseur;
DEALLOCATE curseur;

--- Affichage du resultat du jour (Tri en fonction des besoins !)
SELECT * FROM dbo.compteur_identite
ORDER BY [schema], [table], [index_pk]
 ;

Bien sût cette requête doit être adaptée a vos besoins.

Quelques infos clé :

• Le nom de la table dbo.compteur_identite est arbitraire choisissez celui qui vous fait plaisir ;-)
• Dans la déclaration du curseur sur la liste des tables a surveiller retirer les tables inutiles, moi j'ai retiré le schéma sys et les deux tables 'MASTERKEY' et 'spt_values'. 
• Calcul de la colonne "Critique", j'ai posé un seuil à 1 million de lignes restant le seuil doit être ajusté en fonction de la vitesse de remplissage et de votre capacité à agir lorsqu'il n'y a plus assez de place.
• Les filtres sur les types de colonne sont assez obscur, je les ai déterminé par recherche : Tout ce qui a une collation c'est des colonnes de type texte : pas de problème ici. Tout les type avec une longeur à 0 ou négative (il y en an si si) sont filtré pour que les calculs restent cohérents, les type avec une xprec à 0 sont aussi filtrées (Exemple les type images).
• La requête finale est a ajusté en fonction de vos besoins et peut être jouée autant de fois que nécessaire.

Enjoy !

SQL : Identity dans les tables temporaires

Une Astuce de DOMINIQUE : que je reprend ici en "l'état"

Désolé pour ceux qui peuvent connaitre, mais je viens de le découvrir et ça me manquait notamment pour bosser avec des tables temporaires...

Problème :

  Lors de la création d'un table par

SELECT (...) INTO #foo

  #foo n'a pas d'id créé automatiquement.

Solution :

 SELECT IDENTITY(INT, 1, 1) AS foo_id, (...) INTO #foo

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.