SQL Server Tsql CTE Common Table Expressions

CTE: CTE’s (SQL Common Table Expression ) are more like temporary views than anything else. When you look at the execution plan, you’ll see that they are substituted/inlined into the query but not materialized and stored anywhere. With the exception of recursion, they’re more to make queries simpler to write than faster to run.

CTE: CTE’s are more like temporary views than anything else. When you look at the execution plan, you’ll see that they are substituted/inlined into the query but not materialized and stored anywhere. With the exception of recursion, they’re more to make queries simpler to write than faster to run.

Example below

Say Query is below which is in need to be used multiple times in a Stored Procedure. SQL Server CTE Performance is same as query used in it.

SELECT * FROM (
SELECT A.Address, E.Name, E.Age From EmployeeAddress A
Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME

Rewriting the above query using CTE expressions will be as below

With T(Address, Name, Age) --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from EmployeeAddress A
INNER JOIN Employee E ON E.EID = A.EID
)
SELECT * FROM T --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME


Common Table Expressions have the samilar functionality as a view. They are ideal within Stored Procedure where you don’t necessarily need a view defined for the system. CTE can improve readability of the Query and is very helpful.

CTEs can also be used to recursively enumerate hierarchical data.