WITH common_table_expression (Transact-SQL)

Le linee guida seguenti sono valide per le espressioni di tabella comuni non ricorsive. Per le linee guida relative alle espressioni di tabella comuni ricorsive, vedere "Linee guida per la definizione e l'utilizzo di espressioni di tabella comuni ricorsive" più avanti.

• Un'espressione di tabella comune deve essere seguita da un'istruzione SELECT, INSERT, UPDATE, MERGE o DELETE che faccia riferimento ad alcune o a tutte le colonne CTE. Un'espressione CTE può anche essere specificata in un'istruzione CREATE VIEW come parte dell'istruzione di definizione SELECT della vista.

• In una CTE non ricorsiva è possibile definire più query CTE. Le definizioni devono essere combinate da uno di questi operatori sui set: UNION ALL, UNION, INTERSECT o EXCEPT.

• Una CTE può far riferimento a se stessa e alle CTE definite in precedenza nella stessa clausola WITH. Il riferimento in avanti non è consentito.

• Non è consentito specificare più di una clausola WITH in una CTE. Ad esempio, se una definizione CTE_query_definition include una sottoquery, tale sottoquery non può includere una clausola WITH nidificata che definisce un'altra CTE.

• Le clausole seguenti non possono essere utilizzate in CTE_query_definition:

  • COMPUTE o COMPUTE BY

  • ORDER BY (tranne quando si specifica una clausola TOP)

  • INTO

  • Clausola OPTION con hint per la query

  • FOR XML

  • FOR BROWSE

• Quando un'espressione CTE viene usata in un'istruzione che fa parte di un batch, l'istruzione precedente deve essere seguita da un punto e virgola.

• Una query che fa riferimento a un'espressione CTE può essere utilizzata per definire un cursore.

• L'espressione CTE può fare riferimento alle tabelle nei server remoti.

• Durante l'esecuzione di una CTE, tutti gli hint che fanno riferimento a una CTE possono entrare in conflitto con altri hint individuati quando la CTE accede alle tabelle sottostanti, allo stesso modo degli hint che fanno riferimento alle viste nelle query. In tali circostanze la query restituisce un errore. Per ulteriori informazioni, vedere Risoluzione delle viste.

• Quando una CTE è la destinazione di un'istruzione UPDATE, tutti i riferimenti alla CTE nell'istruzione devono corrispondere. Se, ad esempio, alla CTE è assegnato un alias nella clausola FROM, l'alias deve essere utilizzato per tutti gli altri riferimenti alla CTE. È possibile che riferimenti ambigui a una CTE producano un comportamento del join imprevisto e risultati della query imprevisti. Per ulteriori informazioni, vedere UPDATE (Transact-SQL).

Le linee guida seguenti sono valide per la definizione delle espressioni di tabella comuni ricorsive.

• La definizione CTE ricorsiva deve contenere almeno due definizioni di query CTE, un membro non ricorsivo e un membro ricorsivo. È possibile definire più membri ricorsivi e non ricorsivi, ma tutte le definizioni delle query dei membri non ricorsivi devono precedere la definizione del primo membro ricorsivo. Tutte le definizioni delle query CTEsono membri non ricorsivi tranne nei casi un cui fanno riferimento all'espressione CTE stessa.

• I membri non ricorsivi devono essere combinati da uno degli operatori sui set seguenti: UNION ALL, UNION, INTERSECT o EXCEPT. UNION ALL è l'unico operatore sui set consentito tra l'ultimo membro non ricorsivo e il primo, nonché durante la combinazione di più membri ricorsivi.

• Il numero delle colonne nei membri ricorsivi e non ricorsivi deve essere lo stesso.

• Il tipo di dati di una colonna nel membro ricorsivo deve essere lo stesso del tipo di dati della colonna corrispondente nel membro non ricorsivo.

• La clausola FROM di un membro ricorsivo deve fare riferimento solo una volta all'espressione CTE expression_name.

• I seguenti elementi non sono consentiti nella definizione CTE_query_definition di un membro ricorsivo:

  • SELECT DISTINCT

  • GROUP BY

  • HAVING

  • Aggregazione scalare

  • TOP

  • LEFT, RIGHT, OUTER JOIN (INNER JOIN è consentito)

  • Sottoquery:

  • Hint applicato a un riferimento ricorsivo a un'espressione CTE all'interno di una definizione CTE_query_definition.

Le linee guida seguenti sono valide per l'utilizzo delle espressioni di tabella comuni ricorsive.

• Tutte le colonne restituite dalla CTE ricorsiva ammettono valori Null a prescindere dal supporto dei valori Null delle colonne restituite dalle istruzioni SELECT coinvolte.

• Un CTE ricorsiva formulata in modo non corretto può provocare un ciclo infinito. Ad esempio, se la definizione della query del membro ricorsivo restituisce gli stessi valori per entrambe le colonne padre e figlio, si crea un ciclo infinito. Per evitare un ciclo infinito, è possibile limitare il numero di livelli di ricorsione consentito per una particolare espressione utilizzando l'hint MAXRECURSION e un valore compreso tra 0 e 32.767 nella clausola OPTION dell'istruzione INSERT, UPDATE, MERGE, DELETE o SELECT. Ciò consente di controllare l'esecuzione dell'istruzione fino a quando non viene risolto il problema relativo al codice che sta creando il ciclo. Il valore predefinito per l'intero server è 100. Se è specificato 0, non viene applicato alcun limite. È possibile specificare solo un valore MAXRECURSION per istruzione. Per ulteriori informazioni, vedere Hint per la query (Transact-SQL).

• Non è possibile utilizzare una vista che contiene un'espressione di tabella comune ricorsiva per aggiornare i dati.

• I cursori possono essere definiti sulle query tramite CTE. La CTE corrisponde all'argomento select_statement che definisce il set di risultati del cursore. Sono consentiti solo i cursori fast forward only e statici (snapshot) per le CTE ricorsive. Se viene specificato un altro tipo di cursore in una CTE ricorsiva, il tipo di cursore viene convertito in statico.

• Nelle CTE è possibile far riferimento alle tabelle nei server remoti. Se nel membro ricorsivo della CTE si fa riferimento al server remoto, viene creato uno spool per ogni tabella remota in maniera che si possa accedere alle tabelle in modo locale ripetutamente. Se la query è di tipo CTE, Index Spool o Lazy Spool viene visualizzato nel piano di query con il predicato aggiuntivo WITH STACK associato. Questo è uno dei modi utilizzati per confermare una ricorsione appropriata.

• Le funzioni analitiche e di aggregazione nella parte ricorsiva dell'espressione CTE vengono applicate al set per il livello di ricorsione corrente, non al set per l'espressione CTE. Le funzioni come ROW_NUMBER funzionano solo nel subset di dati passato dal livello di ricorsione corrente e non nell'intero set di dati passato alla parte ricorsiva dell'espressione CTE. Per ulteriori informazioni, vedere K. Using analytical functions in a recursive CTE.

Nell'esempio seguente viene illustrato il numero totale di ordini di vendita per anno per ogni rappresentante di Adventure Works Cycles.

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

Nell'esempio seguente viene illustrato il numero medio di ordini di vendita per tutti gli anni per rappresentante.

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;
GO

Nell'esempio seguente viene illustrato l'elenco gerarchico dei responsabili e dei dipendenti a loro subordinati. All'inizio dell'esempio vengono presentati la creazione e il popolamento della tabella dbo.MyEmployees.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
GO

Nell'esempio seguente vengono illustrati i responsabili e i dipendenti a loro subordinati. Il numero di livelli restituiti è limitato a due.

USE AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
WHERE EmployeeLevel <= 2 ;
GO

Nell'esempio seguente viene utilizzato come base l'esempio C aggiungendo i nomi del responsabile e dei dipendenti e i loro rispettivi titoli. La gerarchia dei responsabili e dei dipendenti viene inoltre evidenziata rientrando ogni livello.

USE AdventureWorks2008R2;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)
    FROM dbo.MyEmployees AS e
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('|    ' , EmployeeLevel) +
        e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '|    ' + FirstName + ' ' + 
                 LastName)
    FROM dbo.MyEmployees AS e
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

È possibile utilizzare MAXRECURSION per impedire che una CTE ricorsiva non corretta provochi un ciclo infinito. Nell'esempio seguente viene creato intenzionalmente un ciclo infinito e viene utilizzato l'hint MAXRECURSION per limitare a due il numero di livelli di ricorsione.

USE AdventureWorks2008R2;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  dbo.MyEmployees AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

Dopo la correzione dell'errore di codifica, MAXRECURSION non è più necessario. Nell'esempio seguente viene illustrato il codice corretto.

USE AdventureWorks2008R2;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM dbo.MyEmployees AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

Nell'esempio seguente viene illustrata la gerarchia di assembly e componenti del prodotto necessari per costruire la bicicletta per ProductAssemblyID = 800.

USE AdventureWorks2008R2;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

Nell'esempio seguente viene aggiornato il valore PerAssemblyQty per tutte le parti utilizzate per realizzare il prodotto "Road-550-W Yellow, 44" (ProductAssemblyID800). Tramite l'espressione di tabella comune viene restituito un elenco gerarchico di parti utilizzate per realizzare ProductAssemblyID 800 e di componenti utilizzati per creare tali parti e così via. Vengono modificate solo le righe restituite dall'espressione di tabella comune.

USE AdventureWorks2008R2;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0; 

Nell'esempio seguente vengono utilizzati più membri ricorsivi e non ricorsivi per restituire tutti i predecessori di una persona specificata. Viene creata una tabella e vengono inseriti i valori per stabilire l'albero genealogico restituito dalla CTE ricorsiva.

-- Genealogy table
IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;
GO
CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT dbo.Person 
VALUES(1, 'Sue', NULL, NULL)
      ,(2, 'Ed', NULL, NULL)
      ,(3, 'Emma', 1, 2)
      ,(4, 'Jack', 1, 2)
      ,(5, 'Jane', NULL, NULL)
      ,(6, 'Bonnie', 5, 4)
      ,(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM dbo.Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM dbo.Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, dbo.Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, dbo.Person
WHERE Generation.ID = Person.ID;
GO

Nell'esempio seguente viene illustrata una trappola in cui si può cadere quando si utilizza una funzione analitica o di aggregazione nella parte ricorsiva di un'espressione CTE.

DECLARE @t1 TABLE (itmID int, itmIDComp int);
INSERT @t1 VALUES (1,10), (2,10); 

DECLARE @t2 TABLE (itmID int, itmIDComp int); 
INSERT @t2 VALUES (3,10), (4,10); 

WITH vw AS
 (
    SELECT itmIDComp, itmID
    FROM @t1

    UNION ALL

    SELECT itmIDComp, itmID
    FROM @t2
) 
,r AS
 (
    SELECT t.itmID AS itmIDComp
           , NULL AS itmID
           ,CAST(0 AS bigint) AS N
           ,1 AS Lvl
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID) 

UNION ALL

SELECT t.itmIDComp
    , t.itmID
    , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N
    , Lvl + 1
FROM r 
    JOIN vw AS t ON t.itmID = r.itmIDComp
) ;

SELECT Lvl, N FROM r

Di seguito vengono riportati i risultati previsti per la query.

Lvl  N
1    0
1    0
1    0
1    0
2    4
2    3
2    2
2    1

Di seguito vengono riportati i risultati effettivi per la query.

Lvl  N
1    0
1    0
1    0
1    0
2    1
2    1
2    1
2    1

N restituisce 1 per ogni sessione della parte ricorsiva dell'espressione CTE perché solo il subset di dati per tale livello di ricorsione viene passato a ROWNUMBER. Per ognuna delle iterazioni della parte ricorsiva della query, viene passata solo una riga a ROWNUMBER.