مقدمه

دستور WITH در T-SQL امکان تعریف Common Table Expression را پیش از یک Statement فراهم می‌کند. CTE غیر بازگشتی یک نتیجه منطقی نام‌گذاری‌شده است که خوانایی Queryهای چندمرحله‌ای را افزایش می‌دهد. این مقاله از نحو پایه شروع می‌کند و با چند CTE، Window Function، مدیریت NULL، گزارش سازمانی، رفع خطای Semicolon و الگوی SARGable ادامه می‌یابد.

این موضوع بخشی از راهنمای جامع Common Table Expressions در SQL Server است. CTE فقط در همان SELECT، INSERT، UPDATE، DELETE یا MERGE بلافاصله پس از تعریف قابل دسترسی است؛ بنابراین آن را با View یا جدول موقت که دامنه و رفتار متفاوتی دارند یکسان ندانید.

تعریف، نحو و اجزای WITH

نام CTE بعد از WITH می‌آید و فهرست نام ستون‌ها اختیاری است. Query داخل AS باید یک مجموعه‌نتیجه معتبر برگرداند. اگر عبارت ستونی بدون نام باشد یا نام‌ها باید تغییر کنند، فهرست ستون‌ها را صریح بنویسید. چند تعریف با ویرگول جدا می‌شوند و تنها یک کلمه WITH در ابتدای زنجیره قرار می‌گیرد.

;WITH CteName (ColumnA, ColumnB) AS
(
    SELECT ExpressionA, ExpressionB
    FROM dbo.SourceTable
    WHERE SearchCondition
)
SELECT ColumnA, ColumnB
FROM CteName;

CTE پارامتر مستقل ندارد؛ پارامترهای Stored Procedure یا متغیرهای Batch می‌توانند در Query تعریف استفاده شوند. نوع خروجی از عبارت‌های SELECT استنتاج می‌شود. ORDER BY معمولاً باید در مصرف‌کننده نهایی قرار گیرد و وجود TOP یا OFFSET به معنی تضمین ترتیب بیرونی نیست.

ده مثال عملی WITH

;WITH Colors AS
(
    SELECT * FROM (VALUES (1, N'آبی'), (2, N'سبز')) V(ColorID, ColorName)
)
SELECT ColorID, ColorName FROM Colors;

CREATE TABLE #Customers(CustomerID int, CustomerName nvarchar(50), IsActive bit);
INSERT #Customers VALUES (1,N'سپهر',1),(2,N'آرمان',0);
;WITH ActiveCustomers AS
(
    SELECT CustomerID, CustomerName FROM #Customers WHERE IsActive = 1
)
SELECT * FROM ActiveCustomers;
DROP TABLE #Customers;

;WITH Orders AS
(
    SELECT * FROM (VALUES
        (101, CAST(1000 AS decimal(10,2)), CAST(100 AS decimal(10,2))),
        (102, CAST(800 AS decimal(10,2)), CAST(0 AS decimal(10,2)))
    ) V(OrderID, GrossAmount, DiscountAmount)
)
SELECT OrderID, GrossAmount - DiscountAmount AS NetAmount FROM Orders;

;WITH Sales AS
(
    SELECT * FROM (VALUES (1,600),(1,500),(2,300)) V(SellerID, Amount)
), SellerTotals AS
(
    SELECT SellerID, SUM(Amount) AS TotalAmount FROM Sales GROUP BY SellerID
)
SELECT SellerID, TotalAmount FROM SellerTotals WHERE TotalAmount >= 1000;

;WITH Numbers AS
(
    SELECT * FROM (VALUES (1),(2),(3)) V(N)
), Squares AS
(
    SELECT N, N * N AS SquareValue FROM Numbers
)
SELECT N, SquareValue FROM Squares;

;WITH ProductSales AS
(
    SELECT * FROM (VALUES (N'A',900),(N'B',1200),(N'C',700)) V(ProductCode, Amount)
), Ranked AS
(
    SELECT ProductCode, Amount, ROW_NUMBER() OVER(ORDER BY Amount DESC) AS RN
    FROM ProductSales
)
SELECT ProductCode, Amount FROM Ranked WHERE RN <= 2;

;WITH InvoiceData AS
(
    SELECT * FROM (VALUES
        (1, CAST(500 AS decimal(10,2)), CAST(NULL AS decimal(10,2))),
        (2, CAST(300 AS decimal(10,2)), CAST(25 AS decimal(10,2)))
    ) V(InvoiceID, Amount, Discount)
), Normalized AS
(
    SELECT InvoiceID, Amount, COALESCE(Discount,0) AS Discount FROM InvoiceData
)
SELECT InvoiceID, Amount - Discount AS Payable FROM Normalized;

;WITH Transactions AS
(
    SELECT * FROM (VALUES
        (N'فروش', CAST(1200 AS decimal(10,2))),
        (N'فروش', CAST(800 AS decimal(10,2))),
        (N'پشتیبانی', CAST(500 AS decimal(10,2)))
    ) V(DepartmentName, Amount)
), DepartmentReport AS
(
    SELECT DepartmentName, COUNT(*) AS ItemCount, SUM(Amount) AS TotalAmount
    FROM Transactions GROUP BY DepartmentName
)
SELECT * FROM DepartmentReport ORDER BY TotalAmount DESC;

DECLARE @Minimum int = 2;
;WITH ValuesToRead AS
(
    SELECT * FROM (VALUES (1),(2),(3)) V(ID)
)
SELECT ID FROM ValuesToRead WHERE ID >= @Minimum;

CREATE TABLE #Events(EventID int PRIMARY KEY, EventDate datetime2(0));
CREATE INDEX IX_Events_EventDate ON #Events(EventDate);
INSERT #Events VALUES (1,'2026-07-20T10:00:00'),(2,'2026-07-21T09:00:00');
;WITH DailyEvents AS
(
    SELECT EventID, EventDate FROM #Events
    WHERE EventDate >= '2026-07-20' AND EventDate < '2026-07-21'
)
SELECT EventID, EventDate FROM DailyEvents;
DROP TABLE #Events;

خطاهای رایج

خطای Incorrect syntax near WITH غالباً از بسته‌نشدن Statement قبلی می‌آید. ;WITH مشکل را رفع می‌کند. خطای Invalid object name زمانی رخ می‌دهد که توسعه‌دهنده بخواهد CTE را در Statement بعدی دوباره بخواند. اختلاف تعداد نام ستون‌ها با خروجی SELECT، نام تکراری ستون‌ها و ارجاع رو به جلو میان چند CTE نیز باید بررسی شوند.

یک اشتباه مفهومی مهم، تصور ذخیره‌شدن خودکار نتیجه است. اگر CTE چند بار ارجاع شود، منبع ممکن است دوباره خوانده شود. Actual Execution Plan را بررسی کنید؛ وجود Spool تصمیم Optimizer است و قراردادی همیشگی نیست. برای مرحله سنگین و چندبارمصرف، #TempTable با ایندکس آزمایشی را مقایسه کنید.

ملاحظات Performance و بهترین روش‌ها

فیلترهای انتخابی را نزدیک منبع اعمال کنید، ستون‌های غیرضروری را حذف کنید و تبدیل ضمنی در Join و Predicate را کنترل کنید. CTE مانع استفاده از ایندکس نیست، اما تابع‌گذاری روی ستون ایندکس‌شده یا مقایسه نوع‌های ناسازگار همچنان می‌تواند Scan ایجاد کند. SET STATISTICS IO, TIME و Actual Execution Plan معیارهای بهتری از حدس ظاهری هستند.

تعداد زیاد لایه‌ها همیشه نشانه طراحی خوب نیست. هر CTE باید یک مسئولیت روشن مانند پاک‌سازی، تجمیع یا رتبه‌بندی داشته باشد. نام‌های تجاری، Aliasهای صریح و توضیح کوتاه برای منطق حساس، هزینه بازبینی و نگهداشت را کاهش می‌دهد. برای Queryهای حیاتی، ورودی‌های کوچک، بزرگ، NULL و توزیع نامتوازن را در تست رگرسیون بگنجانید.

• همیشه دامنه یک Statement را به خاطر بسپارید.
• برای جلوگیری از خطای نحوی از ;WITH استفاده کنید.
• SELECT ستاره را در کد تولیدی کنار بگذارید.
• در استفاده چندباره، Temp Table را نیز Benchmark کنید.
• ORDER BY نهایی را در Statement مصرف‌کننده بنویسید.
• قبل از DML، مجموعه هدف را با SELECT کنترل کنید.

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

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

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

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

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

پرسش 3: تفاوت CTE غیر بازگشتی و Subquery چیست؟

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

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

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

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

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

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

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

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

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

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

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

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

سؤال 1: آیا می‌توان چند CTE را با چند WITH نوشت؟

در یک زنجیره، یک WITH نوشته می‌شود و تعریف‌ها با ویرگول جدا می‌شوند. هر تعریف بعدی می‌تواند به تعریف قبلی ارجاع دهد.

سؤال 2: چرا CTE در Statement دوم دیده نمی‌شود؟

دامنه نام CTE فقط Statement بلافاصله پس از تعریف است. برای دامنه طولانی‌تر باید ابزار دیگری مانند Temp Table یا View انتخاب شود.

سؤال 3: نوع داده ستون CTE چگونه تعیین می‌شود؟

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

سؤال 4: آیا CTE قابل به‌روزرسانی است؟

اگر نتیجه قواعد View قابل‌به‌روزرسانی را رعایت کند، UPDATE یا DELETE روی CTE ممکن است. Joinها، تجمیع و ستون محاسباتی می‌توانند محدودیت ایجاد کنند.

سؤال 5: تفاوت CTE و Derived Table چیست؟

CTE نام مستقل و قابلیت زنجیره‌سازی خواناتری دارد؛ Derived Table در همان FROM محلی است. طرح اجرا ممکن است یکسان باشد.

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

با داده نماینده، چند اجرا، Logical Read، CPU، Duration، Actual Plan و مقایسه منصفانه با گزینه جایگزین.

سؤال 7: آیا Hint را داخل تعریف می‌گذارید؟

OPTION Query Hint در Statement بیرونی قرار می‌گیرد. استفاده از Hint باید پس از یافتن علت و با آزمون رگرسیون باشد.

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

نامی که نقش نتیجه را بیان کند؛ مانند EligibleOrders یا MonthlyTotals. نام مبهم، فهم وابستگی‌ها را دشوار می‌کند.

جمع‌بندی

WITH ابزاری دقیق برای بیان مرحله‌ای Query است. از آن برای نام‌گذاری منطق، جداکردن پاک‌سازی از گزارش و هدف‌گیری روشن عملیات DML استفاده کنید، اما آن را تضمین سرعت یا ذخیره نتیجه ندانید. طرح اجرا، ایندکس‌ها و اندازه‌گیری با داده واقعی باید تصمیم نهایی را هدایت کنند.

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