Features and Limitations of Common Table Expressions in Azure
The current implementation of CTEs in Azure Synapse Analytics and Analytics Platform System (PDW) has the following features and limitations:
- A CTE can be only specified in a SELECT statement.
- A CTE can be only specified in a CREATE VIEW statement.
- A CTE can be only specified in a CREATE TABLE AS SELECT (CTAS) statement.
- A CTE can be only specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.
- A CTE can be only specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.
CTE in SQL
The Common Table Expressions (CTE) were introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table was just unsuitable. CTE was introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE view, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.