T-SQL Query Advanced--detailed common table expressions (CTE)

Source: Internet
Author: User


For select query statements, typically, in order to make T-SQL code more concise and readable, Referencing a different result set in one query is broken down by the view, not the subquery. However, the view is a database that exists as a system object, which is a bit extravagant for a result set to be used only once in a stored procedure or user-defined function.

A common table expression (Common table expression) is an attribute introduced after the SQL SERVER 2005 version. A CTE can be thought of as a temporary result set that can be referenced more than once in the next Select,insert,update,delete,merge statement. Use common expressions to make statements clearer and more concise.

In addition, 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

definition of common table expression (CTE)

The definition of a common expression is simple and contains only three parts:

    1. The name of the common table expression (after with)
    2. The name of the column involved (optional)
    3. A SELECT statement (immediately after as)

Prototypes in MSDN:

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 expressions (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:

Of course, one of the benefits of a common table expression is that it can be referenced more than once in the next statement:

I have always emphasized that "in the next statement" means that only the next one is used:

Since the CTE can only be used in the next statement, you can define multiple, separated by commas, when you need to reference multiple CTE in the Next statement:

Recursive common table expressions (CTE)

Recursive common table expressions are much like derived tables (Derived Tables), Refers to a CTE that invokes itself in a statement within a CTE. Unlike derived tables, a CTE can be derived recursively multiple times in a definition. For the concept of recursion, a function or procedure calls itself directly or indirectly, a simple concept diagram of recursion as follows:

A typical example of recursive implementation in C is the Fibonacci sequence:

Long fib (int n) {      if (n==0) return 0; if (n==1) return 1;     

The above C language code can see that to form a recursive function, two parts are required. The first part is the base part, which returns a fixed value that tells the program when to start recursion. The second part is the loop part, which is a function or process that calls itself recursively or indirectly.

For recursive common expressions, the implementation principle is the same, and you need to define two parts in the statement:

    • Basic statement
    • Recursive statements

The two parts of SQL are returned through the Union ALL connection result set:

For example: in Adventurework, I want to know the level at which each employee is located, 0 is the highest

Such complex queries have become so elegant and concise through recursive CTE. This is also the most powerful place for the CTE.

Of course, the more powerful the power, the need to be constrained. If used improperly, recursive CTE may appear infinite recursion. server resources for SQL Server. Therefore, SQL Server has the option option to set a maximum number of recursion times:

Or the above statement, which limits the number of recursion:

Messages that are prompted:

This maximum recursion frequency is often based on the specific business that the data represents , such as the assumption that the company hierarchy is at most 2 levels.


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.

Reprinted from: http://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html

T-SQL Query Advanced--detailed common table expressions (CTE)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.