CTE

CTE (Common Table Expression): Exists only within the scope of the query in which it is defined. It is not stored as a separate object in the database.CTEs are more suitable for simplifying queries and creating temporary result sets within a query,

• 🔸CTEs make code more readable. And readability makes queries easier to debug.
• 🔸Does not store data physically. It is more like a named subquery or a view.
• 🔸 CTEs don't support indexes directly. Performance improvements rely on the optimizer's ability to optimize the query plan.
• 🔸 CTEs can help you perform multi-level aggregations. Use CTEs to store the results of aggregations, which you can then summarize in the main query.



Syntax

   
  
WITH cte_name (column1, column2, ...) AS (
    -- CTE query here
)
SELECT * FROM cte_name;
 
 




---

Lets take an example :

   
  
  -- Suppose you want to find the 3rd highest salary
DECLARE @Nth INT = 3;

WITH RankedSalaries AS (
    SELECT
        Salary,
        DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM
        Employee
)
SELECT DISTINCT
    Salary
FROM
    RankedSalaries
WHERE
    SalaryRank = @Nth;
 
 

Recursive CTE :

🔸 Recursive Common Table Expressions (CTEs) in SQL Server are often used for tasks like traversing hierarchical data or generating sequences.

   
	  
                --  Suppose you want to find 
                  WITH NumberSequence AS (
                    SELECT 1 AS Number
                    UNION ALL
                    SELECT Number + 1
                    FROM NumberSequence
                    WHERE Number < 10
                )

                SELECT Number
                FROM NumberSequence;
	
	 



Codestrz Tech @powered by codestrz