T-SQL common table expression (CTE)

Common table Expressions (CTE)

When writing T-SQL code, it is often necessary to temporarily store some result sets. We have extensively used and introduced two methods for temporarily storing result sets: temporary tables and table variables. In addition to this, you can use the methods of common table expressions. The common table expression (Common table expression) is an attribute introduced by the SQL Server2005 version. The CTE can see that the group is a temporary result set that can be referenced more than once in the next Select,insert,update,delete,merge statement. Use common expression CTE to make statements clearer and more concise. There are temporary tables and table variables that are similar to common expressions. A comparison of three methods is given below.

1. Comparison of 3 methods

(i), temp table: The table structure needs to be created by I/O operations in tempdb in the staging database and deleted automatically once the user launches the SQL Server environment.

(b), table variables: In memory in the form of a table structure, its definition is consistent with the variable, its use is similar to the table, do not need to produce I/O.

(iii), common table expression: Common table expression, which defines a temporary stored result set object that is saved in memory, does not produce I/O and does not need to be defined as a table variable, using a method similar to a table. You can reference it yourself, or it can be referenced more than once in a query.

2, with as meaning

With as-make subquery part (subquery factoring).

It is used to define a SQL fragment that will be used by the entire SQL statement. If the table name of the with as is called more than two times, the optimizer automatically places the data obtained with as as a temporary table, not if it is called only once. The data in the with as phrase can be coerced into the global temporary table via materialize.

With as can be used multiple times with an SQL statement that follows, but cannot be used by multiple SQL statements that follow.

With B as (    select * from XXX WHERE Id > 5) SELECT * FROM B

　　3. Definition of CTE

The definition syntax for a CTE is as follows, consisting mainly of 3 parts.

(a), the name of the EXPRESSION_NAME:CTE expression.

(b), column_name: List of column names.

(iii), CTE_query_definition: Define a SELECT query statement for the CTE result set

)

According to Microsoft's description of the CTE benefits, it boils down to four points:

• Recursive common table Expressions (CTE) can be defined
• A CTE can make it more concise when you don't need to reference the result set as a view by multiple places
• A GROUP by statement can directly act on a scalar column derived from a subquery
• Common table Expressions (CTE) can be referenced more than once in a single statement

Common table (CTE) expressions can be categorized as recursive common table expressions and non-recursive common table expressions, depending on whether recursion is recursive.

　　Non-recursive common table expression (CTE):

A non-recursive common table expression (CTE) is a query result that returns only one result set for an external query call. Does not invoke its own CTE in the statement it defines.

Non-recursive common table expressions (CTE) are used in a way that is consistent with views and subqueries.

For example, a simple non-recursive common table expression:

With Cte_testas (    select * from person_1) SELECT * from Cte_test

One of the benefits of a common table expression is that it can be referenced more than once in the Next statement :

With Cte_test as (    select * from person_1) SELECT * from Cte_test as a--first reference INNER JOIN  Cte_test as B --second reference to a.ID = b.ID ORDER by a.id DESC

Although the above is referenced several times, but only a statement, so it can be executed normally.

If more than one statement is referenced, as in the following, it will be an error.

With Cte_test as (    select * from person_1) SELECT * FROM Cte_test select * from Cte_test

The output results are as follows:

　　

Since the CTE can only be used in the next statement, when you need to refer to multiple CTE in the next statement, you can define multiple, separated by commas, and here is an example of defining multiple CTE at a time:

With Cte_test1as (SELECT * from person_1), cte_test2as (SELECT * from Person_2) SELECT * from Cte_test1unionselect * from Cte_ Test2

The results are as follows:

　　

Recursive common table Expressions (CTE):

For recursive common expressions, you only need to define two parts in the statement:

• Basic statement
• Recursive statements

First build a table column table as follows, column ID, column name, column of the parent column.

　　　

Now using the CTE to query each of its columns is the code of the first layer of the following columns:

With Col_cte (Id,name,parentid,tlevel) as (    --Basic statement    SELECT id,name,parentid,0 as Tlevel from COL    WHERE ParentID = 0    UNION All    --recursive statement    SELECT c.id,c.name,c.parentid,ce.tlevel+1 as Tlevel from COL as C     INNER JOI N Col_cte as CE--recursive call on    C.parentid = CE. ID) SELECT * from Col_cte

The output results are as follows:

　　

0 represents the top-level column. 1 is the Level 1 column. The syntax is very elegant. Just a SELECT * fron col_cte. This is where the CTE is strong, but it has to be constrained, otherwise if unrestricted recursion can consume a lot of system resources. Here's a look at how to limit the maximum number of recursion.

If you change the query syntax above to:

With Col_cte (Id,name,parentid,tlevel) as (    --Basic statement    SELECT id,name,parentid,0 as Tlevel from COL    WHERE ParentID = 0    UNION All    --recursive statement    SELECT c.id,c.name,c.parentid,ce.tlevel+1 as Tlevel from COL as C     INNER JOI N col_cte as CE on     c.parentid = CE. ID) SELECT * from Col_cteoption (maxrecursion 2)--Specify a maximum recursion count of 2

We know that in the above query, to find Tianhe District News at least recursive 3 times, but now only recursion 2 times, what is the result of the run?

　　

The following information is indicated:

MSG 530, Level 16, State 1, line 1th is terminated. The maximum recursion 2 was exhausted before the execution of the statement was completed.

The CTE is a very elegant existence. The greatest benefit of the CTE is the increased readability of code, which is one of the essential qualities of good code. Recursive CTE makes it easier and more enjoyable to implement complex queries in an elegant and concise manner.

T-SQL common table expression (CTE)