مقدمه

Recursive CTE راه استاندارد T-SQL برای تکرار یک Query روی خروجی مرحله پیشین است. این ساختار برای درخت سازمانی، دسته‌بندی چندسطحی، قطعات محصول، مسیرهای وابستگی و تولید دنباله به کار می‌رود. کیفیت پیاده‌سازی به انتخاب Anchor درست، اتصال بازگشتی دقیق و توقف تضمین‌شده وابسته است.

این آموزش زیرمجموعه راهنمای جامع Common Table Expressions است و ده نمونه قابل اجرا را از دنباله ساده تا کنترل چرخه ارائه می‌کند. در محیط تولید، بازگشت را با داده ناسالم، عمق زیاد، چند ریشه و گره یتیم آزمایش کنید.

ساختار Anchor و Recursive Member

Anchor Member بدون ارجاع به نام CTE ردیف‌های شروع را تولید می‌کند. UNION ALL آن را به Recursive Member متصل می‌کند؛ بخش دوم نام CTE را می‌خواند و نسل بعدی را می‌سازد. اجرا وقتی متوقف می‌شود که بخش بازگشتی دیگر ردیفی برنگرداند. تعداد ستون‌ها و نوع‌های دو بخش باید سازگار باشند و Cast صریح برای مسیر، اعداد تجمعی یا سطح از خطا جلوگیری می‌کند.

;WITH RecursiveName AS
(
    SELECT AnchorColumns, 0 AS LevelNo
    FROM dbo.SourceTable
    WHERE ParentID IS NULL
    UNION ALL
    SELECT ChildColumns, R.LevelNo + 1
    FROM dbo.SourceTable AS C
    INNER JOIN RecursiveName AS R ON C.ParentID = R.ID
)
SELECT * FROM RecursiveName
OPTION (MAXRECURSION 100);

MAXRECURSION یک Query Hint در انتهای Statement بیرونی است. پیش‌فرض 100 و محدوده معتبر 0 تا 32767 است؛ صفر محدودیت را حذف می‌کند. این گزینه خطای طراحی را درمان نمی‌کند و باید همراه شرط توقف مبتنی بر داده یا عمق معتبر استفاده شود.

ده مثال عملی Recursive CTE

;WITH N AS
(
    SELECT 1 AS Value
    UNION ALL
    SELECT Value + 1 FROM N WHERE Value < 5
)
SELECT Value FROM N OPTION (MAXRECURSION 10);

DECLARE @StartDate date='2026-07-18', @EndDate date='2026-07-20';
;WITH Dates AS
(
    SELECT @StartDate AS CalendarDate
    UNION ALL
    SELECT DATEADD(day,1,CalendarDate) FROM Dates WHERE CalendarDate < @EndDate
)
SELECT CalendarDate FROM Dates OPTION (MAXRECURSION 100);

WITH E AS
(
    SELECT * FROM (VALUES (1,N'مدیر',CAST(NULL AS int)),(2,N'سرپرست',1),(3,N'کارشناس',2))
    V(ID,Name,ManagerID)
), O AS
(
    SELECT ID,Name,ManagerID,0 AS L FROM E WHERE ManagerID IS NULL
    UNION ALL
    SELECT E.ID,E.Name,E.ManagerID,O.L+1 FROM E JOIN O ON E.ManagerID=O.ID
)
SELECT ID,Name,L FROM O OPTION (MAXRECURSION 20);

WITH C AS
(
    SELECT * FROM (VALUES (1,N'کالا',CAST(NULL AS int)),(2,N'دیجیتال',1),(3,N'رایانه',2))
    V(ID,Title,ParentID)
), T AS
(
    SELECT ID,Title,ParentID,CAST(Title AS nvarchar(400)) AS PathText FROM C WHERE ParentID IS NULL
    UNION ALL
    SELECT C.ID,C.Title,C.ParentID,CAST(T.PathText+N' / '+C.Title AS nvarchar(400)) FROM C JOIN T ON C.ParentID=T.ID
)
SELECT ID,PathText FROM T OPTION (MAXRECURSION 20);

WITH Parts AS
(
    SELECT * FROM (VALUES (1,2,CAST(2 AS decimal(10,2))),(2,3,CAST(4 AS decimal(10,2))))
    V(ParentPartID,ChildPartID,Qty)
), BOM AS
(
    SELECT ParentPartID,ChildPartID,Qty,1 AS L FROM Parts WHERE ParentPartID=1
    UNION ALL
    SELECT P.ParentPartID,P.ChildPartID,B.Qty*P.Qty,B.L+1 FROM Parts P JOIN BOM B ON P.ParentPartID=B.ChildPartID
)
SELECT ChildPartID,Qty,L FROM BOM OPTION (MAXRECURSION 20);

;WITH F AS
(
    SELECT 1 AS N, CAST(1 AS bigint) AS FactorialValue
    UNION ALL
    SELECT N+1, FactorialValue*(N+1) FROM F WHERE N < 6
)
SELECT N,FactorialValue FROM F OPTION (MAXRECURSION 10);

;WITH Weeks AS
(
    SELECT CAST('2026-07-01' AS date) AS WeekStart,1 AS WeekNo
    UNION ALL
    SELECT DATEADD(day,7,WeekStart),WeekNo+1 FROM Weeks WHERE WeekNo < 3
)
SELECT WeekNo,WeekStart FROM Weeks OPTION (MAXRECURSION 10);

WITH Nodes AS
(
    SELECT * FROM (VALUES (1,CAST(NULL AS int)),(2,1),(3,CAST(NULL AS int))) V(ID,ParentID)
), Tree AS
(
    SELECT ID,ParentID,0 AS L FROM Nodes WHERE ParentID IS NULL
    UNION ALL
    SELECT N.ID,N.ParentID,T.L+1 FROM Nodes N JOIN Tree T ON N.ParentID=T.ID
)
SELECT ID,L FROM Tree OPTION (MAXRECURSION 10);

WITH Edges AS
(
    SELECT * FROM (VALUES (1,2),(2,3),(3,1)) V(FromID,ToID)
), Walk AS
(
    SELECT FromID,ToID,CAST(N'/'+CAST(FromID AS nvarchar(10))+N'/'+CAST(ToID AS nvarchar(10))+N'/' AS nvarchar(400)) AS P
    FROM Edges WHERE FromID=1
    UNION ALL
    SELECT E.FromID,E.ToID,CAST(W.P+CAST(E.ToID AS nvarchar(10))+N'/' AS nvarchar(400))
    FROM Edges E JOIN Walk W ON E.FromID=W.ToID
    WHERE W.P NOT LIKE N'%/'+CAST(E.ToID AS nvarchar(10))+N'/%'
)
SELECT FromID,ToID,P FROM Walk OPTION (MAXRECURSION 20);

DECLARE @MaxDepth int=3;
;WITH Levels AS
(
    SELECT 0 AS L
    UNION ALL
    SELECT L+1 FROM Levels WHERE L < @MaxDepth
)
SELECT L FROM Levels OPTION (MAXRECURSION 5);

خطاهای رایج و داده‌های مسئله‌دار

رایج‌ترین مشکل، چرخه‌ای مانند A به B و B به A است. اگر بخش بازگشتی مسیر بازدید یا قاعده توقف نداشته باشد، Query تا MAXRECURSION ادامه می‌یابد و خطا می‌دهد. گره یتیم که ParentID آن در جدول وجود ندارد نیز از Anchor ریشه‌محور دیده نمی‌شود. Constraint، فرایند پاک‌سازی و گزارش دوره‌ای سلامت رابطه باید بخشی از طراحی باشند.

خطای Types do not match معمولاً زمانی رخ می‌دهد که Anchor رشته یا عددی باریک‌تر از عضو بازگشتی تولید کند. CAST هر دو بخش به نوع نهایی، راه‌حل شفاف است. استفاده از varchar برای متن فارسی نیز داده را خراب می‌کند؛ nvarchar و رشته‌های N پیشونددار را به‌کار ببرید.

Performance و بهینه‌سازی

در پیمایش درخت، هر مرحله فرزندان سطح پیشین را جست‌وجو می‌کند؛ بنابراین ایندکس روی ParentID معمولاً حیاتی است. اگر Anchor بر TenantID، وضعیت یا نوع خاصی محدود می‌شود، ایندکس ترکیبی مناسب را با Actual Plan آزمایش کنید. ستون‌های غیرضروری و مسیرهای بسیار پهن Memory Grant و حجم Worktable را افزایش می‌دهند.

Recursive CTE برای عمق منطقی و خروجی کنترل‌شده مناسب است. تولید میلیون‌ها عدد یا تاریخ با این روش معمولاً از Tally Table یا Calendar Table کندتر است. برای گراف متراکم، Closure Table، Graph Table یا الگوریتم مرحله‌ای می‌تواند بهتر باشد. تصمیم را با Logical Read، CPU، Duration، تعداد ردیف هر سطح و اثر هم‌زمانی بسنجید.

• Anchor را تا حد ممکن محدود و دقیق تعریف کنید.
• روی کلید اتصال فرزند به والد ایندکس مناسب بسازید.
• ستون Level و شرط عمق تجاری را نگه دارید.
• چرخه و گره یتیم را در تست داده پوشش دهید.
• MAXRECURSION صفر را فقط با اثبات توقف انتخاب کنید.
• نوع مسیر و محاسبات تجمعی را صریح Cast کنید.

سؤالات متداول

پرسش 1: آیا Recursive CTE داده را به‌صورت دائمی ذخیره می‌کند؟

خیر. Recursive CTE یک نتیجه نام‌گذاری‌شده با دامنه همان دستور است و پس از پایان دستور شیء دائمی باقی نمی‌گذارد. اگر داده باید چند بار پردازش، ایندکس‌گذاری یا میان چند دستور مشترک شود، جدول موقت معمولاً انتخاب مناسب‌تری است. در پروژه‌های بزرگ، بررسی طرح اجرا پیش از تصمیم نهایی اهمیت دارد.

پرسش 2: آیا استفاده از Recursive CTE همیشه Query را سریع‌تر می‌کند؟

خیر. Recursive CTE بیشتر ابزاری برای سازمان‌دهی منطق است و تضمین مادی‌سازی یا بهبود سرعت نمی‌دهد. Optimizer معمولاً تعریف آن را در طرح اصلی ادغام می‌کند؛ بنابراین شاخص‌ها، حجم داده، تخمین کاردینالیتی و شکل Predicateها تعیین‌کننده‌اند. خدمات بازبینی Query می‌تواند گلوگاه واقعی را با Actual Execution Plan مشخص کند.

پرسش 3: تفاوت Recursive CTE و Subquery چیست؟

هر دو می‌توانند نتیجه میانی بسازند، اما Recursive CTE نام مشخص دارد و خوانایی زنجیره تبدیل‌ها یا استفاده چندباره در یک دستور را بهتر می‌کند. Subquery برای منطق کوتاه و محلی مناسب است. انتخاب تجاری درست باید بر نگهداشت‌پذیری، مهارت تیم و طرح اجرای واقعی متکی باشد.

پرسش 4: چه زمانی جدول موقت بهتر از Recursive CTE است؟

وقتی نتیجه میانی حجیم چند بار مصرف می‌شود، نیاز به ایندکس اختصاصی دارد یا باید میان چند دستور باقی بماند، جدول موقت مزیت دارد. Recursive CTE برای یک Statement و منطق مرحله‌ای سبک‌تر است. یک ارزیابی حرفه‌ای باید هزینه نوشتن در tempdb را نیز کنار هزینه محاسبه مجدد مقایسه کند.

پرسش 5: آیا Recursive CTE را می‌توان در UPDATE و DELETE به‌کار برد؟

بله، اگر نتیجه Recursive CTE قابل به‌روزرسانی باشد می‌توان آن را هدف UPDATE یا DELETE قرار داد. این روش برای محدودکردن ردیف‌ها و روشن‌کردن منطق تغییر مفید است؛ بااین‌حال تراکنش، قفل‌ها و نسخه پشتیبان باید جدی گرفته شوند. برای عملیات حساس، اجرای آزمایشی و بازبینی متخصص توصیه می‌شود.

پرسش 6: رایج‌ترین خطای نحوی Recursive CTE چیست؟

خطای رایج، نبودن Semicolon پیش از WITH است؛ به‌خصوص وقتی Statement قبلی خاتمه نیافته باشد. نوشتن همیشگی ;WITH این ابهام Parser را برطرف می‌کند. نام ستون‌های ناسازگار و تعداد ستون متفاوت میان Anchor و بخش بازگشتی نیز از خطاهای پرتکرار هستند.

پرسش 7: برای Performance چه چیزی را اندازه‌گیری کنیم؟

Actual Execution Plan، تعداد Logical Read، زمان CPU، مدت اجرا، Memory Grant، Spill و اختلاف Estimated/Actual Rows را بررسی کنید. SET STATISTICS IO, TIME ON برای آزمایش کنترل‌شده مفید است. در سامانه تولیدی، مانیتورینگ و تحلیل Query Store باید با سیاست امنیتی سازمان هماهنگ شود.

پرسش 8: بهترین روش نام‌گذاری Recursive CTE چیست؟

نام باید نقش داده را بیان کند؛ مانند ActiveCustomers یا MonthlySales، نه نام‌های مبهمی مانند cte1. ستون‌های محاسباتی را نیز صریح نام‌گذاری کنید. این قرارداد در آموزش تیم، بازبینی کد و تحویل پروژه باعث کاهش خطا و هزینه نگهداشت می‌شود.

پرسش 9: Recursive CTE در چه نسخه‌هایی پشتیبانی می‌شود؟

Recursive CTE از SQL Server 2005 در دسترس است و در نسخه‌های جدید SQL Server و Azure SQL نیز پشتیبانی می‌شود. جزئیات محدودیت‌ها و رفتار Engine را باید با نسخه مقصد آزمود. برای مهاجرت سامانه‌های قدیمی، Compatibility Level و Regression Test اهمیت ویژه دارد.

پرسش 10: آیا برای طراحی Recursive CTE می‌توان مشاوره گرفت؟

بله. در Queryهای مالی، گزارش‌های سلسله‌مراتبی و پاک‌سازی داده، طراحی درست Recursive CTE می‌تواند ریسک و پیچیدگی را کم کند. خدمات آموزش، مشاوره و انجام پروژه SQL Server معمولاً شامل بازبینی کد، سنجش طرح اجرا، پیشنهاد ایندکس و مستندسازی تصمیم‌ها است.

سؤالات مصاحبه‌ای

سؤال 1: دو عضو اصلی Recursive CTE چیست؟

Anchor Member نقاط شروع را می‌سازد و Recursive Member با خواندن خروجی قبلی نسل بعدی را ایجاد می‌کند.

سؤال 2: چرا UNION ALL رایج است؟

بازگشت معمولاً به حفظ همه ردیف‌ها نیاز دارد و UNION ALL هزینه حذف تکراری ندارد. کنترل چرخه باید صریح طراحی شود.

سؤال 3: MAXRECURSION صفر چه معنایی دارد؟

محدودیت تعداد بازگشت را حذف می‌کند و در صورت نبود توقف می‌تواند Query بسیار پرهزینه بسازد؛ بنابراین انتخاب پیش‌فرض امنی نیست.

سؤال 4: چگونه عمق را نمایش می‌دهید؟

Anchor سطح صفر یا یک می‌گیرد و عضو بازگشتی مقدار سطح والد به‌علاوه یک را تولید می‌کند.

سؤال 5: چگونه مسیر را می‌سازید؟

در Anchor نام یا شناسه ریشه به نوع nvarchar با طول کافی Cast می‌شود و در بازگشت قطعه فرزند به آن افزوده می‌شود.

سؤال 6: ایندکس مهم کدام است؟

در الگوی والد و فرزند، ایندکس روی ParentID معمولاً یافتن نسل بعدی را تسریع می‌کند؛ ستون‌های Include به خروجی و طرح واقعی وابسته‌اند.

سؤال 7: با گره یتیم چه می‌کنید؟

گزارش سلامت داده می‌سازم، Foreign Key را در صورت سازگاری دامنه اعمال می‌کنم و سیاست تجاری نمایش یا اصلاح یتیم‌ها را مشخص می‌کنم.

سؤال 8: چه زمانی Recursive CTE مناسب نیست؟

برای دنباله بسیار بزرگ، گراف متراکم یا پردازش چندباره سنگین، ساختارهای ازپیش‌محاسبه‌شده یا الگوریتم مرحله‌ای می‌توانند بهتر باشند.

جمع‌بندی

Recursive CTE ابزار قدرتمندی برای حل مسائل سلسله‌مراتبی در یک Statement است. Anchor محدود، Join صحیح، نوع داده سازگار، شرط توقف، کنترل چرخه، MAXRECURSION و ایندکس ParentID اجزای یک راه‌حل قابل اعتماد هستند. پیش از استقرار، داده ناسالم و بیشترین عمق واقعی را آزمایش و طرح اجرا را ثبت کنید.

بازگشت به مقاله جامع Common Table Expressions