T-SQL: les Table Expressions    

Sous T-SQL, on peut écrire des requêtes nommées, ceci revient à écrire une requête complète qui retourne un certain résultat, puis, on lui associe un nom. Cette requête sera appelée à partir d’une requête externe avec le nom qui lui a été donnée.

T-SQL supporte 4 fromes de ces requêtes nommées :

       1.      Tables dérivées

       2.      Common Table Expressions (CTE)

       3.      Vues

       4.      In-line table-valued functions

Les deux premières formes doivent être déclarées dans le même endroit que la requête externe appelante.

Par contre les deux autres sont stockées dans la base de données comme des objets.

      1  .      Les Tables dérivées

Cette requête est déclarée dans le FROM de la requête externe, puis on lui donne un nom qui sera utilisé dans le reste de la requête appelante.

Exp.

Ce type de requêtes est utile lorsqu’on a plusieurs jointures entre plusieurs tables entre-autres ces requêtes. L’alias de cette requête permet de bien préciser la colonne à utiliser.  

      2.      Les CTE

Ces requêtes sont déclarées au début avant la requête de base, elles sont désignées par la clause WITH.

Exp. 

Si on veut déclarer plusieurs CTE dans une même requête, alors on peut les séparer par des virgules. La Clause WITH est déclarée une seule fois au début.

Par la suite, on peut appeler ces tables à partir de la requête de base ou même à partir des CTE eux même.

      3.      Les vues 

Ce sont des requêtes stockées comme objets dans la base de données. Mais, sauf la définition de la table qui est stockée et non pas les données. A chaque fois qu’on appelle la vue, la requête sera exécutée pour récupérer les données.

Exemple de déclaration d’une Vue

CREATE VIEW Sales.RankedProducts

AS

SELECT

ROW_NUMBER() OVER(PARTITION BY categoryid

ORDER BY unitprice, productid) AS rownum,

categoryid, productid, productname, unitprice

FROM Production.Products;

      4.      Les In-line table-valued functions

Consiste à déclarer des requêtes dans des fonctions, ce qui permet d’utiliser des paramètres lors de la sélection.

Exemple de déclaration d’une In-line table-valued functions

CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE

AS

RETURN

WITH EmpsCTE AS

(

SELECT empid, mgrid, firstname, lastname, 0 AS distance

FROM HR.Employees

WHERE empid = @empid

UNION ALL

SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance

FROM EmpsCTE AS S

JOIN HR.Employees AS M

ON S.mgrid = M.empid

)

SELECT empid, mgrid, firstname, lastname, distance

FROM EmpsCTE;