مقدمه و مسیر یادگیری

Common Table Expression که با نام کوتاه CTE شناخته می‌شود، روشی استاندارد برای نام‌گذاری یک مجموعه‌نتیجه موقت در محدوده یک دستور SQL است. این قابلیت منطق پیچیده را به مرحله‌های قابل‌خواندن تقسیم می‌کند، بدون آنکه الزاماً جدول فیزیکی جدیدی ساخته شود. در این راهنما از مبانی نحو WITH تا پیمایش سلسله‌مراتب با Recursive CTE پیش می‌رویم و در هر مرحله تفاوت میان خوانایی، قابلیت نگهداشت و کارایی واقعی را روشن می‌کنیم.

CTE به‌ویژه در گزارش‌گیری سازمانی، محاسبات تجمعی، حذف داده تکراری، آماده‌سازی ورودی عملیات DML و مدل‌سازی ساختارهای درختی کاربرد دارد. نکته کلیدی این است که CTE یک دستور مستقل نیست؛ تعریف آن باید بلافاصله با SELECT، INSERT، UPDATE، DELETE یا MERGE مصرف‌کننده دنبال شود. دامنه نام CTE نیز فقط همان Statement است و در دستور بعدی قابل استفاده نخواهد بود.

برای یادگیری سریع، ابتدا مقاله WITH را برای CTEهای غیر بازگشتی و قواعد نحوی بخوانید؛ سپس به مقاله Recursive CTE بروید تا Anchor Member، Recursive Member، شرط توقف و MAXRECURSION را عمیق بررسی کنید. مثال‌های این صفحه مستقل و قابل اجرا هستند و می‌توان آن‌ها را در یک پایگاه آزمایشی SQL Server اجرا و همراه Actual Execution Plan تحلیل کرد.

• آموزش کامل WITH و CTE غیر بازگشتی در SQL Server
• آموزش کامل Recursive CTE و پیمایش سلسله‌مراتبی

CTE دقیقاً چگونه کار می‌کند؟

تعریف CTE از یک نام، فهرست اختیاری نام ستون‌ها و یک Query محصور در پرانتز تشکیل می‌شود. Optimizer این ساختار منطقی را همراه دستور مصرف‌کننده بهینه می‌کند. برخلاف تصور رایج، نتیجه CTE لزوماً یک‌بار محاسبه و در حافظه ذخیره نمی‌شود؛ هر ارجاع می‌تواند بخشی از طرح نهایی باشد و حتی سبب دسترسی دوباره به منبع شود. بنابراین برای نتیجه سنگین و چندبارمصرف، جدول موقت با ایندکس مناسب را نیز آزمایش کنید.

CTE غیر بازگشتی معمولاً برای شکستن Query به مرحله‌های معنایی استفاده می‌شود. می‌توان چند CTE را پس از یک WITH و با ویرگول تعریف کرد و CTEهای بعدی می‌توانند به تعریف‌های قبلی ارجاع دهند. Forward Reference مجاز نیست؛ یعنی یک تعریف نمی‌تواند CTEای را بخواند که پایین‌تر هنوز تعریف نشده است. همچنین ORDER BY درون تعریف، جز در سناریوهای مجاز مانند TOP یا OFFSET، تضمین ترتیب خروجی ایجاد نمی‌کند.

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

;WITH CteName (Column1, Column2) AS
( 
    SELECT Expression1, Expression2
    FROM dbo.SourceTable
    WHERE SearchCondition
)
SELECT Column1, Column2
FROM CteName;

مقایسه موضوع‌های این مجموعه

WITH و CTE غیر بازگشتی

کلمه WITH نقطه ورود تعریف CTE است. در Queryهای تحلیلی می‌توان نخست داده خام را فیلتر کرد، سپس تجمیع را در CTE دوم انجام داد و در SELECT نهایی فقط شکل نمایشی خروجی را ساخت. این تفکیک سبب می‌شود هر مرحله نامی تجاری و قابل آزمون داشته باشد. بااین‌حال لایه‌های بیش از حد می‌توانند درک طرح اجرا را دشوار کنند؛ بنابراین هر لایه باید مسئولیتی روشن داشته باشد.

CTE غیر بازگشتی جایگزین خودکار View یا Temp Table نیست. View برای منطق قابل استفاده مجدد در چند Statement و مدیریت مجوزها مناسب است؛ Temp Table امکان آمار و ایندکس اختصاصی می‌دهد؛ CTE برای یک Statement و بیان خوانای منطق عالی است. مقاله مستقل WITH نحو، چند CTE، DML، NULL، خطاهای Semicolon و الگوهای SARGable را با ده مثال بررسی می‌کند.

برای مشاهده نحو کامل و ده سناریوی اجرایی، وارد آموزش WITH شوید.

Recursive CTE و داده‌های سلسله‌مراتبی

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

داده ناسالم می‌تواند چرخه ایجاد کند و Query را تا محدودیت بازگشت ادامه دهد. گزینه MAXRECURSION در Statement مصرف‌کننده قرار می‌گیرد، نه داخل تعریف CTE. مقدار پیش‌فرض 100 است، مقدار صفر محدودیت را حذف می‌کند و باید فقط با تضمین توقف استفاده شود. برای درخت‌های بزرگ، ایندکس روی ParentID و تحلیل Logical Read ضروری است.

برای Anchor، Recursive Member، کنترل چرخه و MAXRECURSION، آموزش Recursive CTE را بخوانید.

شش مثال عملی جامع

;WITH ProductPrices AS
(
    SELECT ProductID, ProductName, Price
    FROM (VALUES
        (1, N'مانیتور', CAST(12000000 AS decimal(18,2))),
        (2, N'صفحه‌کلید', CAST(2500000 AS decimal(18,2)))
    ) AS P(ProductID, ProductName, Price)
)
SELECT ProductID, ProductName, Price * 1.10 AS FinalPrice
FROM ProductPrices;

;WITH SalesData AS
(
    SELECT * FROM (VALUES
        (2026, 6, CAST(150 AS decimal(12,2))),
        (2026, 6, CAST(250 AS decimal(12,2))),
        (2026, 7, CAST(300 AS decimal(12,2)))
    ) V(SalesYear, SalesMonth, Amount)
), MonthlySales AS
(
    SELECT SalesYear, SalesMonth, SUM(Amount) AS TotalAmount
    FROM SalesData
    GROUP BY SalesYear, SalesMonth
)
SELECT SalesYear, SalesMonth, TotalAmount
FROM MonthlySales
ORDER BY SalesYear, SalesMonth;

;WITH RawScores AS
(
    SELECT * FROM (VALUES
        (1, N'علی', 18), (2, N'مینا', 20), (3, N'رضا', NULL)
    ) V(StudentID, StudentName, Score)
), ValidScores AS
(
    SELECT StudentID, StudentName, Score
    FROM RawScores
    WHERE Score IS NOT NULL
), RankedScores AS
(
    SELECT StudentName, Score,
           DENSE_RANK() OVER (ORDER BY Score DESC) AS ScoreRank
    FROM ValidScores
)
SELECT StudentName, Score, ScoreRank
FROM RankedScores;

CREATE TABLE #Tasks(TaskID int PRIMARY KEY, Status nvarchar(20), DueDate date);
INSERT #Tasks VALUES
(1, N'باز', '2026-07-01'), (2, N'باز', '2026-08-01');
;WITH OverdueTasks AS
(
    SELECT TaskID, Status
    FROM #Tasks
    WHERE Status = N'باز' AND DueDate < '2026-07-20'
)
UPDATE OverdueTasks SET Status = N'معوق';
SELECT TaskID, Status FROM #Tasks ORDER BY TaskID;
DROP TABLE #Tasks;

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

WITH Employees AS
(
    SELECT * FROM (VALUES
        (1, N'مدیرعامل', CAST(NULL AS int)),
        (2, N'مدیر فنی', 1),
        (3, N'برنامه‌نویس', 2)
    ) V(EmployeeID, EmployeeName, ManagerID)
), Org AS
(
    SELECT EmployeeID, EmployeeName, ManagerID, 0 AS OrgLevel
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT E.EmployeeID, E.EmployeeName, E.ManagerID, O.OrgLevel + 1
    FROM Employees E
    INNER JOIN Org O ON E.ManagerID = O.EmployeeID
)
SELECT EmployeeID, EmployeeName, OrgLevel
FROM Org
OPTION (MAXRECURSION 20);

کارایی، طرح اجرا و انتخاب ابزار مناسب

برای ارزیابی CTE فقط زمان ظاهری اجرای یک نوبت کافی نیست. Cache را دست‌کاری نکنید مگر در محیط آزمایش اختصاصی؛ چند بار اجرا کنید، Logical Read و CPU را با SET STATISTICS IO, TIME اندازه بگیرید و Actual Execution Plan را نگه دارید. اپراتورهای Scan، Sort، Hash Match، Spool و Warningهای مربوط به Spill یا تبدیل ضمنی می‌توانند علت هزینه را نشان دهند.

اگر یک CTE سنگین چند بار در همان Statement ارجاع شود، Engine تعهدی به ذخیره یک‌باره نتیجه ندارد. مقایسه با #TempTable به همراه ایندکس و آمار می‌تواند تفاوت بزرگی نشان دهد. در مقابل، شکستن بی‌دلیل Query کوچک به tempdb هزینه نوشتن، قفل و مدیریت اضافه ایجاد می‌کند. تصمیم باید با داده واقعی، پارامترهای نماینده و بار هم‌زمان سنجیده شود.

Predicateهای SARGable را حفظ کنید. اعمال تابع روی ستون ایندکس‌شده در شرط، تبدیل نوع ناسازگار و الگوی LIKE با Wildcard ابتدایی می‌تواند Seek را از بین ببرد؛ CTE این مشکل را پنهان یا درمان نمی‌کند. نوع داده پارامتر، Collation و تخمین تعداد ردیف‌ها را نیز با تعریف ستون‌ها هم‌راستا نگه دارید.

برای CTE بازگشتی، ایندکس روی کلید اتصال والد و فرزند، کوچک نگه‌داشتن عرض ردیف، حذف ستون‌های غیرضروری و محدودکردن ریشه‌ها بیشترین اثر را دارند. مسیرهای متنی بلند Memory Grant را افزایش می‌دهند. اگر ساختار بسیار عمیق یا گرافی است، HierarchyID، Graph Tables، Closure Table یا پردازش مرحله‌ای ممکن است مناسب‌تر باشد.

خطاهای رایج و روش اصلاح

• فراموش‌کردن Semicolon پیش از WITH؛ الگوی ;WITH را برای جلوگیری از ابهام Parser به‌کار ببرید.
• فرض مادی‌سازی خودکار؛ طرح اجرا را ببینید و در صورت مصرف چندباره، جدول موقت را مقایسه کنید.
• قرار دادن ORDER BY بدون مصرف‌کننده مجاز؛ ترتیب را در SELECT نهایی مشخص کنید.
• استفاده از UNION به‌جای UNION ALL در بازگشت بدون دلیل؛ حذف تکراری هزینه Sort یا Hash اضافه دارد.
• نداشتن شرط توقف یا وجود چرخه؛ کلیدهای بازدیدشده، محدودیت عمق و MAXRECURSION را طراحی کنید.
• انتخاب SELECT ستاره؛ ستون‌های لازم را صریح بنویسید تا قرارداد خروجی و هزینه I/O کنترل شود.

بهترین روش‌ها و چک‌لیست نهایی

• نام CTE را بر اساس نقش تجاری و به زبان ثابت تیم انتخاب کنید.
• فیلترها را زود اعمال کنید، ولی اثر آن را در Actual Execution Plan تأیید کنید.
• تعداد و نوع ستون‌های Anchor و Recursive Member را دقیقاً سازگار نگه دارید.
• MAXRECURSION را متناسب با عمق معتبر دامنه تنظیم کنید.
• برای عملیات DML ابتدا نتیجه CTE را با SELECT و شمارش ردیف کنترل کنید.
• Query Store و تست رگرسیون را برای تغییرهای مهم به کار بگیرید.
• هنگام مصرف چندباره، CTE و Temp Table را با داده واقعی Benchmark کنید.
• منطق پیچیده را مستند کنید و مثال داده مرزی، NULL و چرخه را در تست‌ها بگنجانید.

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

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

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

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

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

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

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

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

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

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

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

پرسش 6: رایج‌ترین خطای نحوی 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: بهترین روش نام‌گذاری CTE چیست؟

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

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

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

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

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

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

سؤال 1: دامنه CTE چیست؟

دامنه از تعریف WITH تا پایان همان Statement مصرف‌کننده است. نام CTE در Statement بعدی وجود ندارد و برای استفاده مجدد باید View، Function، جدول موقت یا تعریف تازه‌ای انتخاب شود.

سؤال 2: آیا CTE یک جدول موقت است؟

خیر. CTE یک عبارت منطقی نام‌گذاری‌شده است و به‌صورت پیش‌فرض شیء فیزیکی یا فضای ذخیره دائمی ندارد. Optimizer درباره اپراتورهای واقعی طرح تصمیم می‌گیرد.

سؤال 3: چرا ;WITH پیشنهاد می‌شود؟

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

سؤال 4: Anchor Member چه نقشی دارد؟

ردیف‌های آغازین بازگشت را تولید می‌کند و نوع داده ستون‌های خروجی را شکل می‌دهد. Recursive Member نسل‌های بعدی را با ارجاع به CTE می‌سازد.

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

با اعتبارسنجی داده، نگهداری مسیر بازدید، جلوگیری از افزودن شناسه تکراری، محدودکردن عمق و MAXRECURSION. انتخاب روش به حجم و شکل داده وابسته است.

سؤال 6: آیا ORDER BY در CTE ترتیب نهایی را تضمین می‌کند؟

خیر. ترتیب رابطه تضمین‌شده نیست و ORDER BY خروجی باید در SELECT نهایی نوشته شود. TOP یا OFFSET فقط قواعد نحوی خاص خود را دارند.

سؤال 7: چه زمانی CTE را به Temp Table تبدیل می‌کنید؟

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

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

درستی نتیجه، توقف قطعی، خوانایی، Logical Read، CPU، طرح اجرا، رفتار با پارامترهای مختلف و هم‌زمانی معیارهای اصلی هستند.

جمع‌بندی

CTE یک ابزار بیان قدرتمند در T-SQL است: منطق مرحله‌ای را خوانا می‌کند، عملیات DML را هدفمند می‌سازد و مسائل سلسله‌مراتبی را با بازگشت حل می‌کند. این قابلیت به‌تنهایی تضمین Performance نیست؛ انتخاب ایندکس، شکل Predicate، تعداد ارجاع و طرح اجرای واقعی همچنان تعیین‌کننده‌اند. با مثال‌های کوچک شروع کنید، داده مرزی را آزمایش کنید و هر تغییر مهم را با آمار I/O و طرح اجرا بسنجید.

ادامه با آموزش تخصصی WITH و ادامه با آموزش تخصصی Recursive CTE