Recursive CTE در واقع یک CTE است که ارجاع به خودش دارد. با این کار ، CTE اولیه بارها و بارها اجرا می شود و زیر مجموعه داده ها را برمی گرداند تا زمانی که result set کامل شود.


هنگامی که با داده های سلسه مراتبی سرو کار داشته باشید، recursive CTE می توانید به کمک شما بیاید. زیرا CTE به کار خود ادامه می دهد(طبق توضیحات فوق، به طور مرتب CTE اجرا می شود تا نتیجه کامل شود) تا اینکه کوئری کل سلسله مراتب را برگرداند. به طور پیش فرض حداکثر سطح بازگشتی که توسط CTE پشتیبانی می شود، 100 می باشد که با استفاده از MAXRECURSION می توان آن را تغییر داد.



Syntax کلی Recursive CTE به صورت زیر می باشد:

WITH cte_name (col1, col2, …)

AS

(

   sql query  -- Anchor member

   UNION ALL

   sql query -- Recursive member that referenced to cte_name, must have

   --termination check

)

-- Statement using the CTE

SELECT *

FROM   cte_name -- Invocation

recursive CTE در تعاریف خود باید شامل قسمتهای زیر باشد:
Invocation: این عبارت از CTE استفاده می کند.
Anchor Member: این قسمت ابتدا اجرا می شود و فقط یک بار فراخوانی می شود.
Recursive Member: این قسمت از کوئری بارها و بارها اجرا می شود تا اینکه ردیف ها کامل شوند و هیچ ردیف دیگری برای بازکرداندن باقی نمانده باشد. نتایج هر اجرا با نتایج قبلی UNION می شود.
Termination check: بررسی اتمام برای اطمینان حاصل کردن از متوقف شدن کوئری می باشد.

در ادامه مثال از Recursive CTE مشاهده خواهید کرد و با طرز کار آن کاملا آشنا خواهید شد.
اکنون به یک مثال ساده از Recursive CTE در SQL Server نگاهی خواهیم داشت. اسکریپت زیر از Recursive CTE استفاده کرده و اعداد را از 1 تا 100 برمی گرداند:

WITH   cte

AS     (SELECT 1 AS n

        UNION ALL

        SELECT n + 1

        FROM   cte

        WHERE  n < 100

       )

SELECT n

FROM   cte;

CTEs default maximum recursion level

به طور پیش فرض حداکثر سطح بازگشتی که توسط CTE پشتیبانی می شود 100 است که پیشتر هم بدان اشاره شد. بصورت پیشفرض تا 100 سطح از سلسله مراتب را می توانید روی CTE حساب کنید. در مثال زیر پا را از 100 فراتر می گذاریم و خواهید دید که کار ناتمام مانده و ارور دریافت خواهیم کرد.

WITH   cte

AS     (SELECT 1 AS n

        UNION ALL

        SELECT n + 1

        FROM   cte

        WHERE  n <= 101

       )

SELECT n

FROM   cte;

همانطور که مشاهده می کنید، در اجرای اسکرپت فوق، خطای maximum recursion 100 has been exhausted before statement completion نمایش داده شد. خطا مذکور همانطور که بالاتر هم توضیح داده شد، این عنی را می دهد که CTE به طور پیشفرض تا 100 سطح را پشتیبانی میکند.

Change the CTE maximum Recursion Level

برای تغییر حداکثر سطح بازگشتی CTE باید از MAXRECURSION استفاده کنید. حداکثر سطح بازگشتی که می توانیم با اشاره به MAXRECURSION مشخص کنیم 32767 است. خب، حالا با استفاده از MAXRECURSION این سطح را تغییر میدهیم و سپس خواهید دید که کوئری فوق بدون مشکل اجرا می شود.

WITH  cte



AS     (SELECT 1 AS n



UNION ALL SELECT n + 1



FROM   cte WHERE  n <= 101



)



SELECT n FROM   cte



OPTION (MAXRECURSION 101)

حداکثر سطح بازگشتی که می توانیم با MAXRECURSION مشخص کنیم 32767 است. فرض کنید ما یک الزام داریم که در آن سطح بازگشتی می تواند از عدد 32767 فراتر رود ، اجازه دهید به این مثال هم بپردازیم. مثلا اعداد بیش از 32767 یعنی 32768 و یا اعداد بزرگتر را تولید کنیم.

WITH cte



AS     (SELECT 1 AS n



UNION ALL SELECT n + 1



FROM   cte WHERE  n <= 326768



)



SELECT n FROM   cte;



OPTION (MAXRECURSION 326768)

همانطور که در ارور فوق مشاهده می کنید، خطای MAXRECURSION Option exceeds the allowed maximum of 32767 برگردانده شده است. برای جلوگیری از چنین خطایی ، ما مقدار MAXRECURSION را 0 تعیین می کنیم.

WITH cte



AS     (SELECT 1 AS n



UNION ALL SELECT n + 1



FROM   cte WHERE  n <= 326768



)



SELECT n FROM   cte;



OPTION (MAXRECURSION 0)

مقدار 0 برای MAXRECURSION به معنای محدودیت در سطح بازگشتی نیست، اگر MAXRECURSION = 0 تعیین شده، پس در این حالت باید اطمینان حاصل شود که کوئری منجر به یک سطح بازگشتی نامحدود نمی شود.