CTE (Nested Common Table Expression) in Fabric data warehousing (Transact-SQL)
Si applica a: 
Endpoint di analisi SQL e Warehouse in Microsoft Fabric
Common Table Expressions (CTEs) può semplificare query complesse decostruendo normalmente query complesse in blocchi riutilizzabili.
Esistono quattro tipi di CTE, tra cui CTE standard, sequenziali, ricorsivi e CTE annidati .
- Un CTE standard non fa riferimento o non definisce un altro CTE nella relativa definizione.
- La definizione di un CTE annidato include la definizione di un'altra CTE.
- La definizione di un CTE sequenziale può fare riferimento a un CTE esistente, ma non può definire un altro CTE.
- Un CTE ricorsivo fa riferimento a se stesso nella relativa definizione.
Fabric Warehouse e endpoint di analisi SQL supportano entrambi gli endpoint CTE standard, sequenziali e annidati (anteprima). Mentre le CTE standard e sequenziali sono disponibili a livello generale in Microsoft Fabric, le CTE annidate sono attualmente una funzionalità di anteprima.
Per altre informazioni sulle espressioni di tabella comuni, vedere WITH common_table_expression (Transact-SQL).
Nota
Durante l'anteprima, la creazione di CTE annidati è supportata solo da SQL Server Management Studio (SSMS). IntelliSense in SSMS non riconosce la sintassi CTE annidata, ma non blocca la creazione di CTE annidati. Per un'esperienza ottimale, limitare i livelli di annidamento a 64.
Sintassi
WITH <NESTED_CTE_NAME_LEVEL1> [ (column_name , ...) ] AS
(WITH <NESTED_CTE_NAME_LEVEL2> [ (column_name , ...) ] AS
(
...
WITH <NESTED_CTE_NAME_LEVELn-1> [ ( column_name , ...) ] AS
(
WITH <NESTED_CTE_NAME_LEVELn> [ ( column_name , ...) ] AS
(
Standard_CTE_query_definition
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn-1
...
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVEL2
)
Linee guida per la creazione e l'uso di un CTE annidato
Oltre alle linee guida per la creazione e l'uso di CTE standard, ecco alcune linee guida aggiuntive per le CTE annidate:
- Un CTE annidato può essere usato solo in un'istruzione SELECT. Non può essere usato nelle istruzioni UPDATE, INSERT o DELETE.
- Nella definizione di un CTE annidato non sono consentite istruzioni UPDATE, INSERT o DELETE.
- I nomi CTE allo stesso livello di annidamento non possono essere duplicati.
- Un CTE annidato è visibile solo agli CTE annidati o alle CTE sequenziali che si trovano a un livello superiore immediato.
- Le query tra database sono consentite in una definizione CTE annidata.
- Gli hint di query (ad esempio la clausola OPTION) non sono consentiti nella definizione di un CTE annidato.
- Il CTE annidato non può essere usato in CREATE VIEW.
- AS OF non è supportato nella definizione di un CTE annidato.
- Le CTE annidate sono supportate in una definizione di sottoquery CTE, ma non in una sottoquery generale.
Esempi
Differenze tra ctes standard, sequenziali e annidati
-- Standard CTE
;WITH Standard_CTE AS (
SELECT * FROM T1
)
SELECT * FROM Standard_CTE;
-- Sequential CTE
;WITH CTE1 AS (
SELECT * FROM T1
),
CTE2 AS (SELECT * FROM CTE1),
CTE3 AS (SELECT * FROM CTE2)
SELECT * FROM CTE3
-- Nested CTE
;WITH OUTER_CTE AS (
WITH INNER_CTE AS (
SELECT * FROM T1
)
SELECT * FROM INNER_CTE
)
SELECT * FROM OUTER_CTE;
L'ambito del nome di CTE è limitato al relativo ambito
I nomi CTE possono essere riutilizzati a diversi livelli di annidamento. I nomi CTE allo stesso livello di annidamento non possono essere duplicati. In questo esempio il nome cte1 viene usato sia nell'ambito esterno che interno.
;WITH
cte1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1 WHERE c2 = 1
)
SELECT * FROM inner_cte1_2
),
cte2 AS (
WITH
cte1 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
),
inner_cte2_2 AS (
SELECT * FROM cte1 WHERE c4 = 1
)
SELECT * FROM inner_cte2_2
)
CTE annidato complesso con unione, unione, intersecamento e ad eccezione di
CREATE TABLE NestedCTE_t1 (
c1 INT,
c2 INT,
c3 INT
);
GO
INSERT INTO NestedCTE_t1
VALUES (1, 1, 1);
INSERT INTO NestedCTE_t1
VALUES (2, 2, 2);
INSERT INTO NestedCTE_t1
VALUES (3, 3, 3);
GO
WITH
outermost_cte_1 AS (
WITH
outer_cte_1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1
UNION SELECT * FROM inner_cte1_1
)
SELECT * FROM inner_cte1_1
UNION ALL SELECT * FROM inner_cte1_2
),
outer_cte_2 AS (
WITH
inner_cte2_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c2 = 1
EXCEPT SELECT * FROM outer_cte_1
),
inner_cte2_2 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
UNION SELECT * FROM inner_cte2_1
)
SELECT * FROM inner_cte2_1
UNION ALL SELECT * FROM outer_cte_1
)
SELECT * FROM outer_cte_1
INTERSECT SELECT * FROM outer_cte_2
),
outermost_cte_2 AS (
SELECT * FROM outermost_cte_1
UNION SELECT * FROM outermost_cte_1
)
SELECT * FROM outermost_cte_1
UNION ALL SELECT * FROM outermost_cte_2;
La CTE annidata è supportata nella definizione di sottoquery CTE, ma non in sottoquery generale
Questa query ha esito negativo con l'errore seguente: Msg 156, Level 15, State 1, Line 3. Incorrect syntax near the keyword 'WITH'.
SELECT * FROM
(
WITH
inner_cte1_1 AS (SELECT * FROM NestedCTE_t1 WHERE c1 = 1),
inner_cte1_2 AS (SELECT * FROM inner_cte1_1)
SELECT * FROM inner_cte1_2
) AS subq1;
I riferimenti a un CTE non possono superare il relativo ambito
Questa query ha esito negativo con l'errore seguente: Msg 208, Level 16, State 1, Line 1. Invalid object name 'inner_cte1_1'.
;WITH
outer_cte_1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1 WHERE c2 = 1
)
SELECT * FROM inner_cte1_2
),
outer_cte_2 AS (
WITH inner_cte2_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
)
SELECT
tmp2.*
FROM
inner_cte1_1 AS tmp1,
inner_cte2_1 AS tmp2
WHERE
tmp1.c4 = tmp2.c4
)
SELECT * FROM outer_cte_2;