Advertisement
Search  
Always will be ready notify the world about expectations as easy as possible: job change page
Jan 2, 2023

Common Table Expression (CTE) in SQL Server

Author:
Rajesh Gami
Source:
Views:
1535

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

Similar
Jul 16, 2021
Author: Robert Sheldon
SQL Server performance tuning can seem overwhelming when you don’t know where to start. In this article Robert Sheldon explains 9 best practices for performance tuning.SQL Server is a powerful and feature-rich database management platform that can support a wide...
24 марта
Автор: Olesia Dudareva
Написание нового кода = ошибки. С этим всё просто.Избавится от ошибок – вот это сложная задача.Программисты привыкли, что в их средствах разработки есть встроенные инструменты, показывающие, какая строка кода сейчас работает, отображают текущее содержимое переменных, выводят сообщения о процессе выполнения...
Jan 13
Author: Weerayut Teja
In the world of web development, building robust and efficient REST APIs is a crucial skill. Whether you are a C# beginner or have some programming experience, creating RESTful APIs using .NET Core and Entity Framework with PostgreSQL is an...
2 days ago
Author: Michael Shpilt
One of the most used databases these days is PostgreSQL (aka Postgres). Its accomplishments include being the most popular DB [among professional developers] according to Stack Overflow survey of 2022, the database in all of the fastest TechEmpower benchmarks, and...
Send message
Email
Your name
*Message


© 1999–2024 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET Framework | .NET Core | C# | ASP.NET | Windows Forms | WPF | HTML5 | CSS3 | jQuery | AJAX | Angular | React | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban