Introduction to recursive CTE
1. Introduction to recursive CTE
Welcome back! Let's look more closely at how to use CTEs to solve tasks recursively.2. The recursive CTE
To learn how to use a recursive CTE, we have to review how a recursive CTE is constructed. In general, the definition consists of four parts. The first part is the definition of the CTE using the WITH AS syntax. The second part is the anchor member. This is a query to initialize the recursion and is executed only once, the first time the CTE is executed. The third part is the recursive member. This query is repeatedly executed until no rows are returned. For each step of recursion, the query calls the result of the previous CTE. For termination, the recursive member needs to have a termination condition. The results of each execution are unioned with the prior results. To clarify, the anchor member is unioned with the recursion member. Finally, the last part is the task to query the defined CTE.3. Guide to using a recursive CTE
It is important to know the following guide to using recursive CTEs. First, the number of recursion steps is limited to 100 by default and this could be increased by setting option maxrecursion to the wanted number of steps, which can be any number up to thirty two thousand seven hundred and sixty seven. Second, we are not allowed to use the statements: group by, having, left, right or outer join, select distinct, subqueries or TOP. Third, we need to have the same number of columns or fields for the anchor and the recursive member. And, fourth, the corresponding data types for the anchor and the recursive member should be equal.4. Recursive example
Now we want to solve a recursion example on our own. This is the calculation of the factorial number. As a reminder, the factorial number of n is defined by the product of all positive integers less than or equal to n. Accordingly, the factorial number of three is the product of one, two, and three, and equals six. The recursive definition is as follows. For iteration one, the result is one. For all following iterations, we take the previous result of the calculation, n factorial and calculate the product with the current iteration plus one.5. Recursive example in code
To create a recursive query, the first step is to define a CTE. We'll name it recursion. Next is the initialization of the recursion by the anchor member. It sets the iterationCounter count to one, and initializes the variable factorial to one. This variable will contain the result. Then we have the recursive member. It performs the recursion and is stopped by a termination condition. In this step, the current iteration is increased and the factorial number is set to the product of the current iterationCounter plus one, and the current saved factorial number. For iteration two, this means that the iterationCounter is two and the factorial number is one multiplied by two. For iteration three, the iterationCounter is three and the factorial number is the product of two and three. In this example, the number of iterationCounter should be less than ten. This is set by a where clause. Finally, we select the CTE getting the result of 3628800 for the factorial number of ten.6. Let's practice!
Now it's time for you to apply your new knowledge. Let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.