SQL CTE, or Common Table Expression, is essentially a temporary result set represented in the form of an expression. Once declared using the WITH clause, it can be referenced in SELECT, INSERT, DELETE, or UPDATE scripts.

SQL CTE (common table expression) Syntax

WITH Alias_Query AS (
SELECT Column1, Column2
FROM dbo.Table
)
SELECT *
FROM Alias_Query;

We define an alias using the WITH clause as a result of a query and we can refer to this alias afterwards in the SELECT statement as shown above. CTE must be used with some sense. If we work with a big amount of data doing complex SQL stuff, CTE will be slower than temp tables.

Example of SQL CTE with WITH clause – non-recursive query

Let’s have a situation. We have the table below with sales and we want to return TOP 10 days based on sales amount. Firstly we need to do a daily ranking (using CTE) and then choose the top 10 days in the SELECT clause.

WITH Revenue_Order_ByDays AS (
  SELECT
    DATEKey,
    ROUND(SUM(Amount),2) AS SUM_Amount,
    RANK() OVER (ORDER BY SUM(Amount) desc) AS Order
  FROM AdventureWorksDW2014.dbo.FactFinance
  GROUP BY DATEKey
)
SELECT *
FROM Revenue_Order_ByDays
WHERE Order <= 10;

As you can see we rank all sales day by day in descending order in CTE. Then we select the TOP 10 days after CTE. UPDATE, INSERT, DELETE can be approached in the same way as shown in the example.

Querying for window functions in this manner is a mistake. That is why we used t-sql CTE with WITH – it doesn’t work

SELECT
  DATEKey,
  ROUND(SUM(Amount),2) AS SUM_Amount,
  RANK() OVER (ORDER BY SUM(Amount) desc) as Order
FROM AdventureWorksDW2014.dbo.FactFinance
GROUP BY DATEKey
HAVING RANK() OVER (ORDER BY SUM(Amount) desc) <= 10;

The script above results in an error: Windowed functions can only appear in the SELECT or ORDER BY clauses