T-sql CTE in SQL Server

Common Table Expression in t-sql is introduced in SQL Server 2005. CTE is a temporary result set used as a derived table and also that can be declared once and reference anywhere in a query. CTE only exist only in the scope of current query. We can also use CTE recursively in a query.

CTE syntax

column name's are optional. when you select all the result columns no need to specify the column name's

WITH CTE_name ( column1, column2, column3...)
( CTE_query select Statement )
SELECT column1, column2, column3... FROM CTE_name;


WITH [cte_table]
( SELECT * FROM table1)

SELECT * FROM [cte_table]

CTE Recursion

Recursion in CTE is limited to 100. That can be modified using MAXRECURSION. Below example shows how to use recursive CTE in a query and MAXRECURSION.

WITH [cte_table]
( SELECT * FROM table1
SELECT count(Column1) from [cte_table]

SELECT * FROM [cte_table] option (MAXRECURSION = 500)

Multiple CTE

Multiple CTE can be used in a query by seperating them using comma. Below example shows how to use multiple CTE in a query

WITH [cte_table]
( SELECT * FROM table1),
[cte_table2] AS
( SELECT * FROM table2)

SELECT * FROM [cte_table] t1 INNER JOIN [cte_table2] t2
ON t1.column1 = t2.column1


Author: SQL DEVELOPER15 Mar 2013 Member Level: Bronze   Points : 2


Below video contains complete information about CTE. It contains clear illustartion about CTE with examples.


Hope this video is useful for you.

Best Regards,
SQl Blogger

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: