Réaliser des compteurs relatifs, avec SQL Server.

Dans certains développements on exige une numérotation de certaines données dépendantes d’une information telle que le client ou l’année. Voyonc comment réaliser une telle chose avec MS SQL Server.

Principe :

Le principe est de réaliser une table de compteurs et d’y associer une procédure afin de récupérer une valeur de compteur.
En effet, comme nous l’avons dit dans cet article, l’utilisation d’un MAX(…) + 1 est la pire abomination en terme de performances, en sus de conduire immanquablement un jour ou l’autre à des télescopage (2 clefs identiques).

Cette table doit s’appuyer sur une table des tables, afin de n’utiliser qu’une clef numérique comme identifiant de la table à comptabiliser…
Voici un exemple d’une telle table de compteur :


CREATE TABLE dbo.T_TABLE_TBL
(TBL_ID       INT NOT NULL IDENTITY PRIMARY KEY,
 TBL_NAME     sysname UNIQUE);
GO
 
CREATE TABLE dbo.T_COMPTEUR_CPT
(CPT_ID       INT NOT NULL IDENTITY PRIMARY KEY,
 TBL_ID       INT NOT NULL FOREIGN KEY REFERENCES T_TABLE_TBL (TBL_ID),
 CPT_FKID     INT NOT NULL,           --> valeur du compteur relatif (n° de client, n° d'exercice...)
 CPT_VALEUR   INT NOT NULL DEFAULT 0,
 CONSTRAINT UK_CPT_TBL_RLT UNIQUE (TBL_ID, CPT_FKID));
GO

La procédure pour récupérer une valeur de compteur est la suivante :


CREATE PROCEDURE dbo.P_GET_NEXT_COMPTEUR  
                 @TABLE_NAME NVARCHAR(128),
                 @CPT_FKID   INT,
                 @NEW_ID     INT OUTPUT
AS
SET NOCOUNT ON;
--> ce comptage existe t-il ?
IF NOT EXISTS(SELECT *
              FROM   dbo.T_COMPTEUR_CPT AS C
                     INNER JOIN dbo.T_TABLE_TBL AS T  
                           ON C.TBL_ID = T.TBL_ID
              WHERE  TBL_NAME = @TABLE_NAME
                AND  C.CPT_FKID = @CPT_FKID)
BEGIN
-- non, alors primo insertion !
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION;
   INSERT INTO dbo.T_TABLE_TBL (TBL_NAME)  
   SELECT @TABLE_NAME
   WHERE  NOT EXISTS(SELECT *
                     FROM   dbo.T_TABLE_TBL
                     WHERE  TBL_NAME = @TABLE_NAME);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
   INSERT INTO dbo.T_COMPTEUR_CPT (TBL_ID, CPT_FKID)
   SELECT TBL_ID, @CPT_FKID
   FROM   dbo.T_TABLE_TBL
   WHERE  TBL_NAME = @TABLE_NAME;
   IF @@ERROR <> 0 GOTO LBL_ERROR;
END;
-- récupéartion de la valeur de la clef en même temps que l'incrémentation se fait
UPDATE C
SET    CPT_VALEUR = CPT_VALEUR + 1,
       @NEW_ID    = CPT_VALEUR + 1
FROM   dbo.T_COMPTEUR_CPT AS C
       INNER JOIN dbo.T_TABLE_TBL AS T  
             ON C.TBL_ID = T.TBL_ID
WHERE  TBL_NAME = @TABLE_NAME
  AND  C.CPT_FKID = @CPT_FKID;
IF @@ERROR <> 0 GOTO LBL_ERROR;
-- gestion transaction
IF @@TRANCOUNT > 0  
   COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RETURN
-- gestion d'erreur
LBL_ERROR:
IF @@TRANCOUNT > 0  
   ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RAISERROR('Impossible de calculer une nouvelle clef relative (procedure dbo.P_GET_NEXT_COMPTEUR )', 16, 1);
GO

Utilisation dans un code :

DECLARE @NEW_ID INT;
EXEC dbo.P_GET_NEXT_COMPTEUR 'titi', 108, @NEW_ID OUTPUT;
SELECT @NEW_ID AS NEW_ID;

Mais comment faire lorqu’il y a plusieurs clef à calculer simultanément comme c’est le cas souvent des imports de données ?

On doit alors passer par une fonction table et modifier légèrement la procédure précédente…

La fonction table, qui calcule simultanément tous les ID :


CREATE FUNCTION F_NUM (@DEBUT INT, @FIN INT)
RETURNS @T TABLE (NUM INT)
AS
BEGIN
   IF @DEBUT > @FIN RETURN;
   IF @DEBUT IS NULL RETURN;
   IF @FIN IS NULL SET @FIN = @DEBUT;
   WHILE @DEBUT <= @FIN
   BEGIN
      INSERT INTO @T VALUES (@DEBUT);
      SET @DEBUT = @DEBUT + 1;
   END
   RETURN;
END;    
GO

La procédure modifiée :


CREATE PROCEDURE dbo.P_GET_NEXT_N_COMPTEUR  
                 @TABLE_NAME NVARCHAR(128),
                 @CPT_FKID   INT,
                 @NB_ID      INT, -- nombre de clef demandées
                 @NEW_ID     INT OUTPUT
AS
SET NOCOUNT ON;
--> ce comptage existe t-il ?
IF NOT EXISTS(SELECT *
              FROM   dbo.T_COMPTEUR_CPT AS C
                     INNER JOIN dbo.T_TABLE_TBL AS T  
                           ON C.TBL_ID = T.TBL_ID
              WHERE  TBL_NAME = @TABLE_NAME
                AND  C.CPT_FKID = @CPT_FKID)
BEGIN
-- non, alors primo insertion !
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION;
   INSERT INTO dbo.T_TABLE_TBL (TBL_NAME)  
   SELECT @TABLE_NAME
   WHERE  NOT EXISTS(SELECT *
                     FROM   dbo.T_TABLE_TBL
                     WHERE  TBL_NAME = @TABLE_NAME);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
   INSERT INTO dbo.T_COMPTEUR_CPT (TBL_ID, CPT_FKID)
   SELECT TBL_ID, @CPT_FKID
   FROM   dbo.T_TABLE_TBL
   WHERE  TBL_NAME = @TABLE_NAME;
   IF @@ERROR <> 0 GOTO LBL_ERROR;
END;
-- récupération de la valeur de la clef en même temps que l'incrémentation se fait
UPDATE C
SET    CPT_VALEUR = CPT_VALEUR + @NB_ID,
       @NEW_ID    = CPT_VALEUR + @NB_ID
FROM   dbo.T_COMPTEUR_CPT AS C
       INNER JOIN dbo.T_TABLE_TBL AS T  
             ON C.TBL_ID = T.TBL_ID
WHERE  TBL_NAME = @TABLE_NAME
  AND  C.CPT_FKID = @CPT_FKID;
IF @@ERROR <> 0 GOTO LBL_ERROR;
-- gestion transaction
IF @@TRANCOUNT > 0  
   COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RETURN;
-- gestion d'erreur
LBL_ERROR:
IF @@TRANCOUNT > 0  
   ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RAISERROR('Impossible de calculer une nouvelle clef relative (procedure dbo.P_GET_NEXT_COMPTEUR )', 16, 1);
GO

Un exemple d’appel :


DECLARE @NEW_ID INT, @NB_ID INT;
SET @NB_ID = 3;
EXEC dbo.P_GET_NEXT_N_COMPTEUR 'titi', 108, @NB_ID, @NEW_ID OUTPUT;
SELECT *
FROM   dbo.F_NUM(@NEW_ID, @NEW_ID + @NB_ID - 1)

--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

Une réflexion au sujet de « Réaliser des compteurs relatifs, avec SQL Server. »

  1. Avatar de azur668azur668

    La fonction SELECT * FROM dbo.F_NUM(@NEW_ID, @NEW_ID + @NB_ID Р1) ne marche pas car @NEW_ID correspond au dernier compteur rajout̩, et non au premier (@NEW_ID = CPT_VALEUR + @NB_ID )

    il faudrait plutôt utiliser
    SELECT * FROM dbo.F_NUM(@NEW_ID – @NB_ID +1 , @NEW_ID)

    A+

Laisser un commentaire