Common Table Expression (CTE) In SQL Server

Simplify complex joins and subqueries using SQL Server Common Table Expressions or CTEs. It also provides a way to query hierarchical data. This article provides a complete overview of CTEs, types of CTEs, benefits, drawbacks, and how to use them with SQL Server.

A Common Table Expression or CTE is a short-lived named result set created from an easy SELECT statement employed in a subsequent SELECT statement. Every SQL CTE is sort of a named query, the result of which is kept in a very virtual table (a CTE) which is referenced later within the main query.

Syntax

WITH cte_expression_name[ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )

To view the CTE results, use a select query with the name of the CTE expression.

Select [Column1,Column2,Column3,Column4...] from cte_expression_name

OR

SELECT * FROM cte_expression_name

Types of CTE(Common Table Expression)

There are 2 types of CTEs,

1. Recursive CTE. 
2. Non-Recursive CTE.

Recursive CTE

A Recursive common table expression (CTE) could be a CTE that references itself. By doing so, the CTE repeatedly executes, and returns subsets of information, till it returns the whole result set.

A recursive CTE is beneficial in querying ranked data adore organization charts wherever one worker reports to a manager or multi-level bill of materials once a product consists of many components, and every component itself additionally consists of many different components.

We'll see how to use a CTE to create a simple recursive query that displays row numbers from 1 to 10.

First, he declared an integer variable as 'RowNo', set the default value to 1, and created our first CTE query as the expression name 'ROW_CTE'. This CTE first displays the default row number, then uses union ALL to increment the row number by 1 until the row number reaches the incremented value of 10. To view the results, use the query of your choice to view the CTE results.

Declare @RowNo int =1;
;with ROW_CTE as  
   (  
      SELECT @RowNo as ROWNO    
		UNION ALL  
      SELECT  ROWNO+1  
  FROM  ROW_CTE  
  WHERE RowNo < 10
    )  
 
SELECT * FROM ROW_CTE 

Output

ROWNO
 1
 2
 3
 4
 5
 6
 7 
 8
 9
 10

Non-Recursive CTE

Non-Recursive CTEs are easy in which the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create an easy Non-Recursive CTE to show the row variety from 1 to 10.

As in keeping with the CTE Syntax, every CTE question will begin with a "With" observed with the aid of using the CTE Expression call with a column list.

Here we had been the usage of only one column as ROWNO. Next is the Query part, right here we write our pick-out question to be executed for our CTE. After developing our CTE question to run the CTE uses the pick out an announcement with the CTE Expression call.

;with ROW_CTE(ROWNO) as  
   (  
     SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM table_name 
WHERE id <= 10
    )  
 
SELECT * FROM ROW_CTE 

Output:
ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Nested CTE (Common Table Expression)

WITH First_CTE 
   AS (SELECT 1 EmpId, 'Rajesh Gami' Name)
	
  ,Second_CTE 
   AS (SELECT EmpID, Name, 'Gujarat' State FROM First_CTE)

SELECT *   FROM Second_CTE