Common Table Expressions (CTE) in Sql server

In this article, I have explained what is Common Table Expression(CTE) with example, Advantages of CTE and Recursive CTE in SQL.

What is CTE ?

A CTE defines a temporary result set which you can then use in a SELECT statement.

It becomes a convenient way to manage complicated queries.CTE is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement.

You can also use a CTE in a CREATE a view, as part of the view’s SELECT query.

Common Table Expressions are defined within the statement using the WITH operator.

Why to Use CTE?

Readability – CTE’s promote readability. Rather than lump all you query logic into one large query, create several CTE’s,

which are the combined later in the statement. This lets you get the chunks of data you need and combine them in a final SELECT.

Substitute for a View – You can substitute a CTE for a view. This is useful if you don’t have permissions to create a view object

or you don’t want to create one as it is only used in this one query.

Recursion – Use CTE’s do create recursive queries, that is queries that can call themselves.

This is useful when you need to work on hierarchical data such as organization charts.

Code Maintablility - In SQL, we will use sub-queries to join the records or filter the records from a sub-query. Whenever we refer the same data or join the same set of records using a sub-query, the code maintainability will be difficult.

A CTE makes improved readability and maintenance.

Limitations – Overcome SELECT statement limitations, such as referencing itself (recursion), or performing GROUP BY using non-deterministic functions.

Ranking – Whenever you want to use ranking function such as ROW_NUMBER(), RANK(), NTILE() etc.