Écrire des requêtes utilisant des tables dérivées

  • 6 minutes

Les tables dérivées vous permettent d’écrire des instructions Transact-SQL plus modulaires, ce qui vous permet de fragmenter les requêtes complexes en éléments mieux gérables. L’utilisation de tables dérivées dans vos requêtes peut également servir de solutions de contournement pour certaines des restrictions imposées par l’ordre logique du traitement des requêtes, comme l’utilisation d’alias de colonne.

Comme pour les sous-requêtes, vous créez des tables dérivées dans la clause FROM d’une instruction SELECT externe. Contrairement aux sous-requêtes, vous écrivez des tables dérivées à l’aide d’une expression nommée qui est logiquement équivalente à une table et qui peut être référencée en tant que table à un autre emplacement dans la requête externe.

Les tables dérivées ne sont pas stockées dans la base de données. Par conséquent, aucun privilège de sécurité spécial n’est requis pour écrire des requêtes à l’aide de tables dérivées, mis à part les droits de sélectionner à partir des objets sources. Une table dérivée est créée au moment de l’exécution de la requête externe et devient hors de portée quand la requête externe se termine. Les tables dérivées n’ont pas nécessairement un impact sur les performances, par rapport à la même requête exprimée différemment. Quand la requête est traitée, l’instruction est décompressée et évaluée par rapport aux objets de base de données sous-jacents.

Retourner des résultats à l’aide de tables dérivées

Pour créer une table dérivée, vous écrivez une requête interne entre parenthèses, suivie d’une clause AS et d’un nom pour votre table dérivée, à l’aide de la syntaxe suivante :

SELECT <outer query column list>
FROM (SELECT <inner query column list>
    FROM <table source>) AS <derived table alias>

Par exemple, vous pouvez utiliser une table dérivée pour récupérer des informations sur les commandes passées par année par différents clients :

SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders) AS derived_year
GROUP BY orderyear;

La requête interne génère un ensemble de commandes et la place dans l’année dérivée de la table dérivée. La requête externe opère sur la table dérivée et résume les résultats. Les résultats se présentent ainsi :

année de commande cust_count
2019 67
2020 86
2021 81

Passer des arguments à des tables dérivées

Les tables dérivées peuvent accepter des arguments transmis à partir d’une routine d’appel, comme un lot ou une fonction Transact-SQL, ou bien une procédure stockée. Vous pouvez écrire des tables dérivées avec des variables locales servant d’espaces réservés. Au moment de l’exécution, les espaces réservés peuvent être remplacés par des valeurs fournies dans le lot ou par des valeurs transmises comme paramètres de la procédure stockée qui a appelé la requête. Cela vous permettra de réutiliser votre code de manière plus flexible, au lieu de réécrire la même requête avec des valeurs différentes à chaque fois.

Par exemple, le lot suivant déclare une variable locale (marquée avec le symbole @) pour l’ID d’employé, puis utilise la capacité de SQL Server à assigner une valeur à la variable dans la même instruction. La requête accepte la variable @emp_id, et l’utilise dans l’expression de table dérivée :

DECLARE @emp_id INT = 9; --declare and assign the variable
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (    
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
    WHERE empid=@emp_id --use the variable to pass a value to the derived table query
) AS derived_year
GROUP BY orderyear;
GO

Quand vous écrivez des requêtes qui utilisent des tables dérivées, gardez à l’esprit les instructions suivantes :

  • Un alias doit être attribué à l’instruction SELECT imbriquée qui définit la table dérivée. La requête externe utilise l’alias dans son instruction SELECT à peu près de la même façon que vous faites référence à des tables avec alias jointes dans une clause FROM.
  • Un alias doit être attribué à toutes les colonnes référencées dans la clause SELECT de la table dérivée. Il s’agit d’une meilleure pratique qui n’est pas toujours obligatoire dans Transact-SQL. Chaque alias doit être unique dans l’expression. Les alias de colonne peuvent être déclarés inline avec les colonnes ou en externe par rapport à la clause.
  • L’instruction SELECT qui définit l’expression de la table dérivée ne peut pas utiliser une clause ORDER BY, sauf si elle comprend également un opérateur TOP, une clause OFFSET/FETCH ou une clause FOR XML. En conséquence, il n’y a pas d’ordre de tri fourni par la table dérivée. Vous triez les résultats dans la requête externe.
  • L’instruction SELECT qui définit la table dérivée peut être écrite pour accepter des arguments sous la forme de variables locales. Si l’instruction SELECT est incorporée dans une procédure stockée, les arguments peuvent être écrits en tant que paramètres pour la procédure.
  • Les expressions de table dérivées imbriquées dans une requête externe peuvent contenir d’autres expressions de table dérivée. L’imbrication est autorisée, mais elle n’est pas recommandée en raison d’une complexité accrue et d’une lisibilité réduite.
  • Une table dérivée ne peut pas être référencée plusieurs fois dans une requête externe. Si vous devez manipuler les mêmes résultats, vous devez définir l’expression de la table dérivée à chaque fois, par exemple, de chaque côté d’un opérateur de jointure JOIN.