[Original] recursive query using CTE in SQL Server

 Directory

1. Background
2. Problem
3. Ideas
4. CTE
5. CTE recursive query
6. Conclusion
7. References

Background

I haven't written a blog for a long time. Recently I encountered a problem: "How can I traverse all the sub-Menus under a parent menu ?" Small and then use the CTE recursive query to solve this problem, sort the records for sharing.

Problem

How to traverse all sub-Menus under a parent menu?

Ideas

• Solve with CTE recursive query
• Write a custom function/Stored Procedure Iteration Algorithm for implementation

CTE

1. Definition
2. Syntax structure
3. CTE guidelines
4. Sample Code

Definition

CTE (Common Table Expressions) is available in Versions later than SQL Server 2005. The specified temporary naming result set, which is called CTE. Similar to a derived table, it is not stored as an object and is only valid during query. Unlike the derived table, the CTE can be referenced by itself or multiple times in the same query. Using CTE can improve code readability without compromising its performance.

Syntax structure

The basic syntax structure of CTE is as follows:

WITH expression_name [(column_name [,... n])]

AS

(CTE_query_definition)

The column name list is optional only when different names are provided for all result columns in the query definition.

The statement for running CTE is:

SELECT <column_list>

FROM expression_name;

CTE guidelines

Note the following guidelines when creating a CTE:

1. It is best to add a prefix "when defining CTE ";"
2. The column fields defined in the CTE must be consistent.
3. The first sentence after CTE with must use the select statement of CTE. That is, the life cycle of CTE only disappears after the first use.
4. The with statement can only be used once in sp.
5. When multiple CTE are defined, only one with keyword can be declared, for example

With test1

As

(

Select *............

),

Test2

(

Select *............

)

Common table expressions (CTE) are a new function of SQL Server. Essentially, CTE is a temporary result set, which only exists in statements that occur. You can CREATE a cte in the select, INSERT, DELETE, or create view statements.

Sample Code

Create test table

Use mastergoif exists (SELECT * from sysobjects where name = 'node') drop table node --- create table node (nid int primary key, parentid int not null, name nvarchar (50) not null)

Insert data

INSERT INTO NODE VALUES(1,0,'aa'),(2,1,'bb'),(3,2,'cc'),(4,2,'dd'),(5,3,'ee'),(6,4,'ff'),(7,5,'gg'),(8,4,'hh'),(9,3,'ii'),(10,5,'jj');

Create a CTE DEMO

; WITH NodeCTE (NID, PARENTID, NAME)
AS
(
Select nid, PARENTID, name from node where name = 'bb'
UNION ALL
Select B. NID, B. PARENTID, B. name from NodeCTE A, node B where B. PARENTID = A. NID
)

SELECT * FROM NodeCTE;

CTE recursive query

1. CTE recursive query Structure
2. CTE recursive query Principle
3. Original node graph and execution CTE result set

CTE recursive query structure (derived from MSDN)

The structure of recursive CTE in Transact-SQL is similar to that of recursive routines in other programming languages. Although recursive routines in other languages return scalar values, recursive CTE can return multiple rows.

Recursive CTE consists of the following three elements:

1. Routine call.

   The first call to recursive CTE includes one or more CTE_query_definitions connected by the union all, UNION, except t, or INTERSECT operators. These query definitions form a benchmark result set of the CTE structure, so they are called "positioning point members ".

   CTE_query_definitions is considered as positioning point members unless they reference the CTE itself. ALL positioning Point member query definitions must be placed before the first recursive member definition, and the union all operator must be used to join the last positioning point member and the first recursive member.

2. Recursive call of the routine.

   Recursive calls include one or more CTE_query_definitions connected by the union all operator that references the CTE itself. These query definitions are called recursive members ".

3. Terminate the check.

   The termination check is implicit. recursion stops when no rows are returned in a call.

Note:

If the recursive CTE combination is incorrect, infinite loops may occur. For example, if the recursive member query defines that the same value is returned for the parent and child columns, an infinite loop will occur. When testing the recursive query results, you can use the MAXRECURSION prompt and the value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement, to limit the number of recursive levels allowed by a specific statement.

CTE recursive query Principle

1. Split CTE expression into "positioning point member" and "recursive member"
2. Run the anchor member to create the first result set T0.
3. When running recursive members, use the previous result set as input (Ti) and Ti + 1 as output
4. Repeat Step 3 until an empty set is returned.
5. Returns the result set and merges T0 to Tn through union all.

Original node graph and execution CTE result set

CTE result set

Conclusion

This article uses the CTE Recursive Method to Solve my previous problems, and it is about to end. For personal experience and ability reasons, please kindly advise me if you are not careful or mislead everyone. I will correct it in time. Thank you!

References

1. Http://msdn.microsoft.com/zh-cn/library/ms186243 (v = SQL .100). aspx
2. CTE of MSDN Library: http://msdn.microsoft.com/zh-cn/library/ms175972 (SQL .100). aspx
3. DB2 recursive SQL: http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1010liush/index.html
4. Data point: Generic table expression: http://msdn.microsoft.com/zh-cn/magazine/cc163346.aspx#S2