MENU
  1. Accueil
  2. SQL
  3. SQL concernant l’utilisation des tables temporaires et des variables de table

SQL concernant l’utilisation des tables temporaires et des variables de table

SQL

Lorsqu’il s’agit de stocker temporairement des données en SQL, il est essentiel de comprendre quand utiliser une table temporaire ou une variable de table. Bien que ces deux approches offrent des fonctionnalités similaires, elles ont des caractéristiques et des scénarios d’application différents. Cet article explore en détail les spécificités des tables temporaires et des variables de table, et propose des conseils pour faire le meilleur choix.

Sommaire

Caractéristiques des tables temporaires

Les tables temporaires sont utilisées pour stocker temporairement des données dans SQL, et elles sont généralement préfixées par # ou ##.

Méthode de création

Une table temporaire est créée avec la commande CREATE TABLE. Par exemple :

CREATE TABLE #TempTable (
    ID INT,
    Name NVARCHAR(50)
);

Utilisation

Les tables temporaires conviennent lorsque vous devez stocker de grandes quantités de données temporairement et les utiliser à plusieurs reprises dans plusieurs instructions.

Performance

Les tables temporaires enregistrent les données sur le disque, garantissant une performance stable même pour de grandes quantités de données. Cependant, elles sont sensibles aux E/S disque.

Portée

La portée d’une table temporaire est limitée à la session ou au lot. Elle est automatiquement supprimée à la fin de la session.

-- Exemple d'utilisation dans une session
INSERT INTO #TempTable (ID, Name) VALUES (1, 'Alice');
SELECT * FROM #TempTable;

Caractéristiques des variables de table

Les variables de table sont traitées comme des variables en SQL et sont principalement utilisées pour des manipulations temporaires de données.

Méthode de déclaration

Une variable de table est déclarée avec la commande DECLARE. Par exemple :

DECLARE @TableVar TABLE (
    ID INT,
    Name NVARCHAR(50)
);

Utilisation

Les variables de table sont idéales pour manipuler de petites quantités de données ou pour un usage temporaire dans une procédure stockée.

Performance

Les variables de table stockent les données en mémoire, offrant ainsi de meilleures performances pour les petites manipulations de données. Toutefois, elles sont moins adaptées pour les opérations sur de grandes quantités de données.

Portée

La portée d’une variable de table est limitée au lot ou à la procédure stockée dans laquelle elle est déclarée. Elle est libérée automatiquement une fois que le lot ou la procédure est terminé.

-- Exemple d'utilisation dans un lot
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice');
SELECT * FROM @TableVar;

Comparaison des performances

La performance des tables temporaires et des variables de table dépend de la quantité de données et des opérations effectuées. Voici une comparaison illustrée par des exemples concrets.

Dans le cas de petites quantités de données

Les variables de table offrent de meilleures performances pour les petites quantités de données. Comme elles sont stockées en mémoire, elles n’ont pas de surcharge due aux E/S disque.

-- Exemple où la variable de table offre de meilleures performances
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @TableVar;

Dans le cas de grandes quantités de données

Les tables temporaires sont plus adaptées pour les grandes quantités de données. Comme elles stockent les données sur disque, elles sont moins limitées par la mémoire et permettent la création d’index.

-- Exemple d'utilisation des tables temporaires pour de grandes quantités de données
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name)
SELECT ID, Name FROM LargeSourceTable;
CREATE INDEX idx_temp ON #TempTable(ID);
SELECT * FROM #TempTable;

Présence d’index

Les tables temporaires permettent la création d’index pour améliorer les performances des requêtes. En revanche, les variables de table ont des limitations concernant la création d’index, ce qui peut réduire leurs performances dans des requêtes complexes.

Traitement de requêtes complexes

Les tables temporaires offrent des performances stables même lors de requêtes complexes ou de jointures. Les variables de table ne mettent pas en cache certains plans de requête, ce qui peut entraîner une dégradation des performances dans ces cas-là.

-- Exemple d'utilisation des tables temporaires dans des requêtes complexes
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name)
SELECT ID, Name FROM LargeSourceTable;
SELECT t1.ID, t2.Name
FROM #TempTable t1
JOIN AnotherTable t2 ON t1.ID = t2.ID;

Différences de portée et de durée de vie

Les tables temporaires et les variables de table ont des portées et des durées de vie différentes. Comprendre ces différences est essentiel pour choisir la méthode la plus adaptée.

Portée des tables temporaires

La portée d’une table temporaire est limitée à la session ou au lot dans lequel elle est créée. Une fois la session terminée, la table temporaire est automatiquement supprimée. Les tables temporaires locales (#TempTable) sont accessibles uniquement dans la session en cours, tandis que les tables temporaires globales (##TempTable) peuvent être accessibles par d’autres sessions.

-- Exemple de portée d'une table temporaire
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
-- La table #TempTable est supprimée à la fin de la session

Portée des variables de table

Les variables de table ne sont valides que dans le lot ou la procédure stockée où elles ont été déclarées. Elles sont automatiquement libérées à la fin de ce lot ou de la procédure.

-- Exemple de portée d'une variable de table
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
-- La variable de table @TableVar est libérée à la fin du lot

Différences de durée de vie

La durée de vie des tables temporaires dépend de celle de la session. Si la session se poursuit longtemps, la table temporaire restera active pendant toute cette période. En revanche, les variables de table sont libérées dès que le lot ou la procédure stockée est terminé.

Exemples d’application en fonction de la portée et de la durée de vie

Si vous devez partager des données entre plusieurs lots ou procédures dans une même session, les tables temporaires sont préférables. Pour des besoins temporaires limités à un seul lot ou une seule procédure, les variables de table sont plus appropriées.

-- Exemple d'application d'une table temporaire
CREATE TABLE #SessionTemp (ID INT, Name NVARCHAR(50));
INSERT INTO #SessionTemp (ID, Name) VALUES (1, 'Alice');

-- Accès à partir d'un autre lot ou d'une autre procédure stockée
SELECT * FROM #SessionTemp;

-- Exemple d'application d'une variable de table
DECLARE @BatchVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @BatchVar (ID, Name) VALUES (1, 'Alice');
SELECT * FROM @BatchVar;
-- La variable de table est libérée à la fin du lot

Scénarios d’application des tables temporaires et des variables de table

Les tables temporaires et les variables de table sont chacune adaptées à des scénarios spécifiques. Voici quelques exemples concrets d’application.

Scénarios où les tables temporaires sont appropriées

Les tables temporaires sont particulièrement efficaces dans les scénarios suivants :

Manipulation de grandes quantités de données

Les tables temporaires sont adaptées pour stocker de grandes quantités de données temporairement et les manipuler à plusieurs reprises dans plusieurs instructions. Comme elles sont enregistrées sur disque, elles ne sont pas limitées par la mémoire.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) SELECT ID, Name FROM LargeTable;
SELECT * FROM #TempTable WHERE ID > 1000;

Création d’index

Les tables temporaires sont appropriées lorsque vous avez besoin de créer des index pour améliorer la performance des requêtes.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) SELECT ID, Name FROM LargeTable;
CREATE INDEX idx_temp ON #TempTable(ID);
SELECT * FROM #TempTable WHERE ID > 1000;

Partage de données entre sessions

Les tables temporaires sont utiles lorsque vous devez partager des données dans l’ensemble de la session, par exemple entre plusieurs procédures stockées ou lots.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) VALUES (1, 'Alice');
-- Accès à partir d'un autre lot ou d'une autre procédure stockée
SELECT * FROM #TempTable;

Scénarios où les variables de table sont appropriées

Les variables de table sont particulièrement efficaces dans les scénarios suivants :

Manipulation de petites quantités de données

Les variables de table sont adaptées pour manipuler de petites quantités de données. Comme elles sont stockées en mémoire, elles offrent des performances rapides.

DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @TableVar;

Stockage temporaire de données

Les variables de table sont pratiques lorsque vous avez besoin de stocker temporairement des données dans un seul lot ou une procédure stockée.

DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice');
-- La variable de table est libérée à la fin du lot
SELECT * FROM @TableVar;

Utilisation dans les déclencheurs

Les variables de table sont également appropriées pour stocker temporairement des données dans un déclencheur. Étant donné que la portée des déclencheurs est limitée, la légèreté des variables de table est un avantage.

CREATE TRIGGER trgAfterInsert ON SampleTable
AFTER INSERT AS
BEGIN
    DECLARE @InsertedData TABLE (ID INT, Name NVARCHAR(50));
    INSERT INTO @InsertedData (ID, Name)
    SELECT ID, Name FROM Inserted;
    -- Manipulation des données dans le déclencheur
    SELECT * FROM @InsertedData;
END;

Bonnes pratiques en SQL

Voici quelques bonnes pratiques pour utiliser efficacement les tables temporaires et les variables de table, et améliorer ainsi les performances et l’efficacité de vos requêtes SQL.

Choix en fonction de la quantité de données

Pour optimiser les performances, utilisez des variables de table pour de petites quantités de données et des tables temporaires pour de grandes quantités de données.

-- Utilisation des variables de table pour de petites quantités de données
DECLARE @SmallData TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @SmallData (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @SmallData;

-- Utilisation des tables temporaires pour de grandes quantités de données
CREATE TABLE #LargeData (ID INT, Name NVARCHAR(50));
INSERT INTO #LargeData (ID, Name) SELECT ID, Name FROM LargeSourceTable;
SELECT * FROM #LargeData;

Utilisation des index

Créer des index sur les tables temporaires peut améliorer les performances des requêtes manipulant de grandes quantités de données.

CREATE TABLE #IndexedTable (ID INT, Name NVARCHAR(50));
INSERT INTO #IndexedTable (ID, Name) SELECT ID, Name FROM LargeSourceTable;
CREATE INDEX idx_temp ON #IndexedTable(ID);
SELECT * FROM #IndexedTable WHERE ID > 1000;

Conception basée sur la portée

En concevant vos requêtes en fonction de la portée et de la durée de vie des données, vous éviterez une consommation inutile de ressources.

-- Utiliser des tables temporaires pour les données partagées dans toute la session
CREATE TABLE #SessionData (ID INT, Name NVARCHAR(50));
INSERT INTO #SessionData (ID, Name) VALUES (1, 'Alice');
-- Accessible hors de la portée
SELECT * FROM #SessionData;

-- Utiliser des variables de table pour les données limitées au lot
DECLARE @BatchData TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @BatchData (ID, Name) VALUES (1, 'Alice');
-- Libérée à la fin du lot
SELECT * FROM @BatchData;

Facilité de maintenance

Les variables de table sont automatiquement libérées à la fin du lot, ce qui évite les fuites de mémoire et facilite leur maintenance. Elles sont particulièrement recommandées dans les procédures stockées.

-- Exemple d'utilisation d'une variable de table dans une procédure stockée
CREATE PROCEDURE SampleProcedure
AS
BEGIN
    DECLARE @ProcData TABLE (ID INT, Name NVARCHAR(50));
    INSERT INTO @ProcData (ID, Name) VALUES (1, 'Alice');
    SELECT * FROM @ProcData;
END;

Tests et suivi des performances

Il est essentiel de tester chaque scénario d’utilisation et de surveiller les performances réelles afin de faire les meilleurs choix pour optimiser vos requêtes SQL.

-- Effectuer des tests en fonction des scénarios réels
-- Utiliser des outils de suivi des performances pour vérifier l'efficacité

Conclusion

Voici un récapitulatif des points clés pour différencier les tables temporaires et les variables de table.

Les tables temporaires sont adaptées aux opérations sur de grandes quantités de données et lorsque la création d’index est nécessaire, et elles permettent de partager les données dans toute la session. Les variables de table, quant à elles, sont plus adaptées aux petites opérations de données ou aux besoins temporaires dans un lot ou une procédure stockée, permettant des manipulations rapides en mémoire. En choisissant la méthode la plus adaptée à votre scénario, vous optimiserez les performances de vos requêtes SQL.

SQL
Performance SQL Indexation Table Temporaire Grandes Données Périmètre Variable de Table Stockage de Données Petites Données
Sommaire