What is CTE (Common Table Expression) in SQL Server?

CTE (Common Table Expression)

Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

Where CTE (Common Table Expression) result set will be stored?

CTE results are not stored anywhere, they don’t produce results, a CTE is just a definition, just like a VIEW is just a definition. Think of a CTE as being a View that only lasts for the duration of the query.

Syntax for creating a CTE:

WITH cte_name (Column1, Column2, ..)
AS
( CTE_query )

SQL query using CTE:

With EmpCount(DepId, TotalEmp)
as
(
 Select DepId, COUNT(*) as TotalEmp
 from tblEmp
 group by DepId
)

Select DeptName, TotalEmp
from tblDep
join EmpCount
on tblDep.DeptId = EmpCount.DepId
order by TotalEmp

CTE can only be referenced by a SELECT, INSERT, UPDATE, or DELETE statement, that immediately follows the CTE. If you try to do something else in between, we get an error stating – ‘Common table expression defined but not used’.

IT is also, possible to create multiple CTE using a single WITH clause.