T-SQL CTE

Introduction to T-SQL CTE

T-SQL CTE is defined as, CTE stands for Common Table Expression, which can acknowledge us to describe the provisional result set which is available provisionally while implementing the INSERT, UPDATE, DELETE or MERGE statements; it can have two types of CTEs such as recursive CTE, in which it can be called repeatedly until the condition in the query will not get satisfied. Non-recursive CTE has been utilized to show the numbers from 1 to 10, and it does not use any recursion, which we can say that it is a result set that can persist in memory for executing a single query.

What is T-SQL CTE?

The CTE (Common Table Expression) is the result set that we can recommend with SQL common statements such as SELECT, INSERT, UPDATE, and DELETE; the CTE can be utilized to append the ‘WITH’ clause before the SELECT, INSERT, UPDATE, and DELETE statements in which that clause can carry one or more CTEs which are divided by using the comma, in which we can say that the CTE can be recommended to any table and the CTE result set is not available to any statement when we try to implement any statement.

It has two types of CTE, Recursive CTE, and Non-recursive CTE, in which the recursive CTE can be utilized repeatedly in the looping process. It can be called by itself until the condition gets satisfied. Non-recursive CTEs are simple in that they cannot use any recursion, which, with its help, we can generate the simple non-recursive CTE to show the number from 1 to 10.

Creating T-SQL CTE

Let us see how to create CTE in T-SQL:

Syntax:

WITH usual_expression_name [(column_name[,…..])]
AS
  (CTE_definition)
SQL_statements;

• First, we have to define the expression name in the usual expression name, which we can use belatedly in the query.
• After that, we have to define the list of divided columns using a comma, in which whatever comma we have used must equal the number of columns described in the column definition.
• Then we have to use the ‘AS’ keyword behind the expression name or column list if we have defined the list of columns.
• Now we can use the SELECT statement whose result set can occupy the common table expression.
• At last, we have to define the common table expression within the query under the statement in SQL by using its queries.

Using Recursive Common

A recursive CTE is one of the common expressions that can be recommended under that CTE; this CTE can be helpful when we try to work with hierarchical data, same as when it attempts to implement the query, which can give back the complete hierarchy.

This CTE can be used when we find that the CTE has been generated wrongly to which it can be accepting the infinite loop; the recursive CTE has been utilized repeatedly in looping the statements in which the query can call itself till the query gets satisfied, means up to the conditions in that will not get true and terminating the CTE we can able to give where the condition.

Let us see how to use the recursive common table expression to show the row numbers from 1 to 10 with the help of CTE.

• First, we have to declare the integer variable as ‘RowNo’ with the default value ‘1,’ and then we can generate the first CTE query by using the expression ‘LineCTE’; in our CTE, we can show the default line or row number, and then we can able to utilize a ‘Union ALL’ for and then it can show the number 1 by one up to the Row No can come to the increased value. To display the output, we can utilize the SELECT query.

Code:

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

Output:

Examples of T-SQL CTE

Different examples are mentioned below:

Example #1

Example of simple T-SQL CTE.

In this example, we have described the cte_sales_quantity, which can be the name of the CTE in which it can give back a result that carries three columns staff, year, and sales that can be extracted from using a query; after that, we can create a query which can give back a query of total sales amount with the help of staff and year.

Code:

WITH cte_sales_quantity (staff, sales, year) AS (
SELECT
 f_name + ' ' + l_name,
 SUM(total * record_price * (1 - discount)),
 YEAR(system_date)
FROM
 sales.systemss t
INNER JOIN sales.system_items i ON i.system_id = o.system_id
INNER JOIN sales.staffs s ON s.staff_id = t.staff_id
GROUP BY
f_name + ' ' + l_name,
year(system_date)
)
SELECT
 staff,
 sales
FROM
 cte_sales_quantity
WHERE
 year = 2017;

Output:

Example #2

For example, to make report averages with the help of CTE by using counts.

In this example, we have utilized the sales like the name of the CTE; we can omit the column list, which can be obtained from the CTE statement, and we have used the staff_id and system count.

Code:

WITH cte_sales AS (
 SELECT
  staff_id,
  COUNT(*) system_count
FROM
 sales.systems
WHERE
 YEAR(system_date) = 2017
GROUP BY
 staff_id
)
SELECT
 AVG(system_count) average_orders_by_staff
FROM
 cte_sales;

Output:

Which below query has been utilized to show the result set, which can show the CTE expression in c sales, and this query can give back the number of systems in 2017 using staff sales.

Code:

SELECT
 staff_id,
 COUNT (*) system_count
FROM
 sales. systems
WHERE
 YEAR (system_date) = 2017
GROUP BY
 staff_id;

Conclusion

In this article, we conclude that the CTE in T-SQL can be generated by using the ‘WITH’ clause, and also, if we want to use CTE, then we do not need to generate and drop the table, so this article will help to understand the concept of CTE in the T-SQL.

Recommended Articles

This is a guide to T-SQL CTE. Here we discuss the introduction, creating T-SQL CTE, using recursive common and examples. You may also have a look at the following articles to learn more –

1. SQL ORDER BY DESC
2. SQL EXECUTE
3. PL/SQL NOT EQUAL
4. SQL NOT IN