Common Table Expressions or CTEs
A CTE (Common Table Expression) is a one-time result set that only exists for the duration of the query.
It allows us to refer to data within a single SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, or MERGE statement's execution scope.
It makes complex joins and subqueries easier. It also provides a way to query hierarchical data, such as an organizational hierarchy.
It is temporary because its result cannot be stored anywhere and will be lost as soon as a query's execution is completed
Performance
A DBA always preferred CTE to use as an alternative to a Subquery/View.
CTE Syntax in SQL Server
The CTE syntax includes a CTE name, an optional column list, and a statement/query that defines the common table expression (CTE).
After defining the CTE, we can use it as a view in a SELECT, INSERT, UPDATE, DELETE, and MERGE query.
CTE does not support following clasues
ORDER BY unless you also use as TOP clause
INTO
OPTION clause with query hints
FOR BROWSE
Multiple CTE
In some cases, we'll need to create multiple CTE queries and join them together to see the results.
We need to use the comma operator to create multiple CTE queries and merge them into a single statement.
Why do we need CTE?
CTEs can make it easier to write and manage complex queries by making them more readable and simple.
Some of its use cases are given below:
It is useful when we need to define a derived table multiple times within a single query.
It is useful when we need to create an alternative to a view in the database.
It is useful when we need to perform the same calculation multiple times on multiple query components simultaneously.
It is useful when we need to use ranking functions like ROW_NUMBER(), RANK(), and NTILE().
Some of its advantages are given below:
CTE facilitates code maintenance easier.
CTE increases the readability of the code.
It increases the performance of the query.
CTE makes it possible to implement recursive queries easily.
Types of CTE in SQL Server
SQL Server divides the CTE (Common Table Expressions) into two broad categories:
a. Non-Recursive CTE
A common table expression that doesn't reference itself is known as a non-recursive CTE.
B. Recursive CTE
A common table expression is known as recursive CTE that references itself.
When we execute a recursive query, it repeatedly iterates over a subset of the data.
A recursive CTE must have a UNION ALL statement and a second query definition that references the CTE itself in order to be recursive.
Use Recursive CTE and show Manager name along with each Employee Name and Employee Level
Disadvantages of CTE
CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.
The CTE can only be referenced once by the Recursive member.
We cannot use the table variables and CTEs as parameters in stored procedures.
We know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.
Since it's just a shortcut for a query or subquery, it can't be reused in another query.
The number of columns in the CTE arguments and the number of columns in the query must be the same
