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 - Connaître le format de réponse d'une requête sans pour autant l'exécuter

On a de temps en temps besoin de connaitre les informations renvoyées par une requête avant de l'exécuter.
Par exemple pour savoir quelles vont être les colonnes.
Ou dans quand on renvoie plusieurs tables et qu'on a besoin que le Dataset reçu soit toujours identique, alors que dans certains contextes une ou plusieurs tables ne doivent pas être calculées.

Par exemple :
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto';

Ce DataSet renvoie 2 tables :

• une table des id et noms de contact
• une tables avec l'id du contact et ses informations d'adresses.

On peut donc mettre en place un programme qui lise les 2 tables et remplisse comme il faut les informations dans une liste de contacts qui on un nom et une liste d'adresses le concernant.
Imaginons que dans une des interfaces, je n'ai pas besoin de calculer les adresses, mais que je veuille toujours utiliser le même programme / les mêmes objets pour charger les données.

Il me faut donc modifier le SQL pour remonter les mêmes infos, sans que le calcul de la seconde table se fasse.

1) Solution instinctive
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto'
    AND 1= 0
;

On ajoute une condition qui renvoie toujours faux à la requête : Ça marche, mais Ouais bof !


2) seconde solution la plus sexy à mon gout
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';

SET FMTONLY ON;
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto';
SET FMTONLY OFF;

On entoure ici la requête par un SET FMTONLY (ON au début et OFF pour rétablir). Cela a pour effet que SQL Server renvoie une table vide avec les bonnes colonnes.


Voila voila !


Sachez aussi qu'a partir de SQL SERVER 2012 il existe aussi une procédure stockée : sp_describe_first_result_set qui renvoie une table contenant toutes les informations sur les colonnes issues du texte de la requête passée à la procédure.
voir la documentation MSDN


Enjoy !