مقدمه

Indexed View پاسخ SQL Server به بخشی از نیازهای Materialized View است. ابتدا View با قواعد سخت‌گیرانه ساخته می‌شود و سپس یک Unique Clustered Index روی آن قرار می‌گیرد؛ از این لحظه نتیجه View به‌صورت فیزیکی ذخیره و هم‌زمان با تغییر جدول‌های پایه نگهداری می‌شود.

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

هر INSERT، UPDATE و DELETE مرتبط باید ایندکس View را نیز به‌روز کند. فضای ذخیره‌سازی، Log، Lock و زمان تراکنش افزایش می‌یابد. به همین دلیل طراحی بدون Baseline ممکن است خواندن را سریع و کل سامانه را کندتر کند.

این مقاله قواعد فنی و روش تصمیم‌گیری را با مثال پوشش می‌دهد. برای مقایسه با Viewهای معمولی و پارتیشن‌بندی‌شده، مقاله مادر انواع Views در SQL Server را باز کنید.

تعریف، Syntax و اجزای اصلی

تعریف زیر الگوی عمومی این موضوع را نشان می‌دهد. نام Schema را صریح بنویسید، فهرست ستون‌ها را کنترل کنید و Script را در Source Control نگه دارید تا انتشار در محیط‌های مختلف تکرارپذیر باشد.

CREATE VIEW dbo.ViewName
    WITH SCHEMABINDING
    AS
        SELECT KeyColumn,
               COUNT_BIG(*) AS RequiredRowCount,
               SUM(DeterministicExpression) AS AggregateValue
        FROM dbo.BaseTable
        GROUP BY KeyColumn;
    GO
    CREATE UNIQUE CLUSTERED INDEX CUX_ViewName
    ON dbo.ViewName(KeyColumn);

سازوکار و نکات فنی پیشرفته

SCHEMABINDING وابستگی View به اشیای پایه را رسمی می‌کند و حذف یا تغییر ناسازگار ستون مرجع را مسدود می‌سازد. تمام نام جدول‌ها باید دوبخشی و مالک اشیا با الزامات SQL Server سازگار باشد.

Unique Clustered Index نخستین ایندکس اجباری است، چون SQL Server به کلیدی برای شناسایی یکتای هر ردیف مادی‌شده نیاز دارد. پس از آن می‌توان Nonclustered Index افزود، ولی هر ایندکس هزینه نگهداری جدا دارد.

گزینه‌های SET مانند ANSI_NULLS، QUOTED_IDENTIFIER، ANSI_WARNINGS، ARITHABORT و NUMERIC_ROUNDABORT باید مقادیر موردنیاز داشته باشند. این الزام فقط زمان ساخت نیست؛ Sessionهای تغییر داده و Query نیز باید سازگار باشند.

تعریف View باید قطعی و دقیق باشد. توابع وابسته به زمان جاری، Random، Metadata Session یا عبارت‌های غیرقطعی مجاز نیستند. برخی ساختارها مانند OUTER JOIN، UNION، زیرQuery و Window Function نیز با محدودیت روبه‌رو هستند.

در View تجمیعی COUNT_BIG(*) الزام فنی مهمی است. نوع داده عبارت SUM را طوری انتخاب کنید که هم مجاز و هم در برابر سرریز مقاوم باشد؛ تبدیل صریح اغلب قرارداد را روشن‌تر می‌کند.

Optimizer ممکن است بدون اشاره مستقیم به View از آن برای پاسخ Query پایه استفاده کند که رفتار آن به Edition و شرایط بستگی دارد. Hint NOEXPAND برای دسترسی مستقیم قابل آزمایش است، ولی باید از Hint بی‌دلیل پرهیز کرد.

آمار ایندکس View مانند هر ایندکس دیگری بر تخمین اثر دارد. نگهداری Statistics، Fragmentation و فضای دیسک باید وارد برنامه عملیات شود؛ مادی‌سازی، مسئولیت عملیاتی جدید ایجاد می‌کند.

برای حذف Indexed View ابتدا مصرف Queryها و وابستگی Hintها را بررسی کنید. Drop Index نتیجه را به View معمولی برمی‌گرداند، ولی می‌تواند Plan و SLA را ناگهان تغییر دهد؛ انتشار مرحله‌ای و Rollback لازم است.

مثال‌های عملی

مثال 1: ساخت حداقل Indexed View

نمونه پایه یک View دارای SCHEMABINDING و سپس Unique Clustered Index می‌سازد. این ایندکس نخستین گام اجباری برای مادی‌سازی View است.

SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE VIEW dbo.vw_ProductPrice
    WITH SCHEMABINDING
    AS
        SELECT ProductID, UnitPrice
        FROM dbo.Products;
    GO
    CREATE UNIQUE CLUSTERED INDEX CUX_vw_ProductPrice
    ON dbo.vw_ProductPrice(ProductID);

کلید ایندکس باید هر ردیف View را یکتا کند. اگر خروجی View یکتا نیست، ابتدا Grain داده را دقیق تعریف کنید.

مثال 2: آماده‌سازی جدول و گزینه‌های SET

Indexed View به مجموعه مشخصی از گزینه‌های Session وابسته است. این نمونه گزینه‌های مهم را پیش از ساخت و استفاده تنظیم می‌کند تا رفتار قطعی باقی بماند.

SET NUMERIC_ROUNDABORT OFF;
    SET ANSI_NULLS ON;
    SET ANSI_PADDING ON;
    SET ANSI_WARNINGS ON;
    SET ARITHABORT ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    SELECT SESSIONPROPERTY('ARITHABORT') AS ArithAbort,
           SESSIONPROPERTY('ANSI_NULLS') AS AnsiNulls;

Connection Pool یا ابزار قدیمی ممکن است SET Options متفاوتی اعمال کند. تنظیمات Driver و Session را در محیط عملیاتی نیز کنترل کنید.

مثال 3: تجمیع فروش با COUNT_BIG

برای گروه‌بندی در Indexed View وجود COUNT_BIG(*) لازم است. مجموع مبلغ با نوع داده قطعی محاسبه می‌شود و کلید مشتری Grain خروجی را تشکیل می‌دهد.

CREATE VIEW dbo.vw_SalesByCustomer
    WITH SCHEMABINDING
    AS
        SELECT CustomerID,
               COUNT_BIG(*) AS SaleCount,
               SUM(ISNULL(Amount, CONVERT(decimal(18,2),0))) AS TotalAmount
        FROM dbo.Sales
        GROUP BY CustomerID;
    GO
    CREATE UNIQUE CLUSTERED INDEX CUX_vw_SalesByCustomer
    ON dbo.vw_SalesByCustomer(CustomerID);

SUM روی عبارت Nullable و انتخاب نوع داده باید با محدودیت‌های Indexed View سازگار باشد. احتمال سرریز مجموع را برای داده بلندمدت محاسبه کنید.

مثال 4: خواندن مستقیم با NOEXPAND

در برخی Editionها یا شرایط Optimizer می‌توان برای آزمایش استفاده مستقیم از ایندکس View، Hint مربوط را به کار برد. این Hint باید با طرح اجرای واقعی مقایسه شود.

SELECT CustomerID, SaleCount, TotalAmount
    FROM dbo.vw_SalesByCustomer WITH (NOEXPAND)
    WHERE CustomerID = 10;

NOEXPAND راه‌حل همیشگی نیست و ممکن است آزادی Optimizer را محدود کند. عملکرد حالت با Hint و بدون Hint را در Query Store و بار واقعی مقایسه کنید.

مثال 5: ترکیب با Query تجمعی مصرف‌کننده

گزارش مدیریتی فقط مشتریان با فروش بالا را می‌خواهد. Predicate روی کلید و مقدار از خروجی ازپیش‌تجمیع‌شده اعمال می‌شود.

DECLARE @MinimumAmount decimal(18,2) = 50000000;
    SELECT CustomerID, SaleCount, TotalAmount
    FROM dbo.vw_SalesByCustomer WITH (NOEXPAND)
    WHERE TotalAmount >= @MinimumAmount
    ORDER BY TotalAmount DESC;

برای فیلترهای پرتکرار روی TotalAmount می‌توان Nonclustered Index ثانویه روی Indexed View را بررسی کرد، اما هزینه نگهداری آن هم به عملیات نوشتن افزوده می‌شود.

مثال 6: مدیریت NULL به شکل قطعی

مقادیر NULL در مبلغ فروش باید معنای کسب‌وکاری مشخص داشته باشند. در این نمونه NULL به صفر تبدیل می‌شود تا SUM قابل پیش‌بینی باشد.

SELECT CustomerID,
           SUM(ISNULL(Amount, CONVERT(decimal(18,2),0))) AS SafeTotal
    FROM dbo.Sales
    GROUP BY CustomerID;
    GO
    SELECT CustomerID, TotalAmount
    FROM dbo.vw_SalesByCustomer WITH (NOEXPAND);

تبدیل NULL به صفر فقط وقتی درست است که نبود مبلغ از نظر دامنه واقعاً معادل صفر باشد. این تصمیم باید در مستند مدل داده ثبت شود.

مثال 7: تشخیص عبارت غیرقطعی ممنوع

توابعی مانند GETDATE در Indexed View قطعی نیستند و امکان ایجاد ایندکس را از بین می‌برند. تاریخ مرجع باید به‌عنوان داده ذخیره یا در Query مصرف‌کننده اعمال شود.

-- روش نادرست در Indexed View:
    -- SELECT OrderID, GETDATE() AS ReadAt FROM dbo.Orders;
    
    -- روش اصلاح‌شده:
    CREATE VIEW dbo.vw_OrderStable
    WITH SCHEMABINDING
    AS
        SELECT OrderID, OrderDate, CustomerID
        FROM dbo.Orders;
    GO
    SELECT OrderID, OrderDate, SYSUTCDATETIME() AS ReadAtUtc
    FROM dbo.vw_OrderStable;

Determinism و Precision توابع را پیش از طراحی بررسی کنید. وابسته کردن داده فیزیکی View به زمان جاری از نظر نگهداری نیز تعریف روشنی ندارد.

مثال 8: سناریوی داشبورد موجودی

داشبورد انبار جمع ورود و خروج هر کالا را پیوسته می‌خواند. View تجمیعی تعداد ردیف و خالص گردش را نگه می‌دارد.

CREATE VIEW dbo.vw_InventoryBalanceAgg
    WITH SCHEMABINDING
    AS
        SELECT ProductID,
               COUNT_BIG(*) AS MovementCount,
               SUM(CONVERT(bigint, QuantityDelta)) AS Balance
        FROM dbo.InventoryMovements
        GROUP BY ProductID;
    GO
    CREATE UNIQUE CLUSTERED INDEX CUX_vw_InventoryBalanceAgg
    ON dbo.vw_InventoryBalanceAgg(ProductID);

عدد منفی می‌تواند هشدار کمبود موجودی باشد، اما View جای کنترل Transaction و قیدهای کسب‌وکار را نمی‌گیرد. نوع bigint خطر سرریز جمع گردش را کاهش می‌دهد.

مثال 9: رفع خطای نام یک‌بخشی جدول

SCHEMABINDING نیازمند ارجاع دوبخشی به اشیا است. استفاده از FROM Sales خطا می‌دهد و باید Schema صریح نوشته شود.

-- نادرست:
    -- FROM Sales
    
    -- درست:
    CREATE VIEW dbo.vw_SaleKeys
    WITH SCHEMABINDING
    AS
        SELECT SaleID, CustomerID
        FROM dbo.Sales;
    GO
    CREATE UNIQUE CLUSTERED INDEX CUX_vw_SaleKeys
    ON dbo.vw_SaleKeys(SaleID);

Schema صریح علاوه بر الزام فنی، Resolution نام و وابستگی ماژول را شفاف‌تر می‌کند. مالک View و جدول را نیز هماهنگ نگه دارید.

مثال 10: اندازه‌گیری هزینه خواندن و نوشتن

تصمیم نهایی با Benchmark گرفته می‌شود. خواندن Dashboard و سپس یک عملیات نوشتن کنترل‌شده با STATISTICS IO و TIME سنجیده می‌شود.

SET STATISTICS IO ON;
    SET STATISTICS TIME ON;
    SELECT CustomerID, TotalAmount
    FROM dbo.vw_SalesByCustomer WITH (NOEXPAND)
    WHERE CustomerID BETWEEN 10 AND 100;
    
    BEGIN TRANSACTION;
    UPDATE dbo.Sales
    SET Amount = Amount + 1000
    WHERE SaleID = 5001;
    ROLLBACK TRANSACTION;
    SET STATISTICS TIME OFF;
    SET STATISTICS IO OFF;

آزمون نوشتن را در محیط غیرعملیاتی و با حجم نماینده اجرا کنید. Indexed View فقط وقتی ارزشمند است که سود خواندن از هزینه نگهداری، فضا و پیچیدگی بیشتر باشد.

خطاهای رایج

خطاهای زیر در بازبینی کد، Migration و تحلیل Incident زیاد دیده می‌شوند. هر مورد باید به یک Test یا Guard در فرایند انتشار تبدیل شود، زیرا کشف آن پس از رشد داده یا تغییر نسخه پرهزینه‌تر است.

• فراموش کردن COUNT_BIG در View دارای GROUP BY
• استفاده از GETDATE یا تابع غیرقطعی در تعریف
• نام یک‌بخشی جدول به‌جای dbo.TableName
• تنظیم نبودن ARITHABORT یا NUMERIC_ROUNDABORT
• انتخاب کلید Clustered غیر یکتا برای Grain خروجی
• استفاده از نوع داده مستعد سرریز در SUM
• افزودن ایندکس‌های متعدد بدون سنجش هزینه DML
• فرض استفاده خودکار Optimizer در همه Editionها
• Benchmark فقط روی خواندن و نادیده گرفتن Log و Lock نوشتن
• استفاده از NOEXPAND بدون مقایسه Plan جایگزین

ملاحظات کارایی و بهینه‌سازی

Baseline باید دست‌کم مدت Query، CPU، Logical Reads، نرخ DML، اندازه Log و زمان تراکنش را شامل شود. تنها عدد زمان یک SELECT برای تصمیم معماری کافی نیست.

پس از ساخت، Plan خواندن را بررسی کنید که آیا Index Seek یا Scan روی View رخ داده و چه تعداد ردیف خوانده شده است. NOEXPAND را به‌عنوان آزمایش کنترل‌شده بسنجید.

تأثیر نوشتن را با Batch مشابه تولید آزمایش کنید. به‌روزرسانی یک ستون غیرمرتبط هم بسته به تعریف و ایندکس‌ها ممکن است هزینه داشته باشد و Lock Duration را افزایش دهد.

کلید Unique Clustered باریک و پایدار، فضای ایندکس‌های ثانویه را کاهش می‌دهد. کلید عریض در تمام Nonclustered Indexها تکرار و مصرف حافظه و دیسک را بیشتر می‌کند.

ایندکس ثانویه روی ستون فیلتر پرتکرار می‌تواند Lookup یا Scan را کاهش دهد، ولی باید نسبت سود خواندن به نگهداری محاسبه شود. Missing Index Suggestion به‌تنهایی دستور اجرا نیست.

Query Store را قبل و بعد انتشار نگه دارید تا Regression Queryهای دیگر دیده شود. تغییر Schema، Statistics یا Compatibility Level می‌تواند نحوه تطبیق Query با View را تغییر دهد.

بهترین روش‌ها

1. فقط Queryهای پرتکرار و گران را نامزد کنید.
2. تمام SET Options را در Connectionها استاندارد کنید.
3. Grain و کلید یکتای واقعی خروجی را اثبات کنید.
4. Determinism و Precision تمام عبارت‌ها را بررسی کنید.
5. برای Aggregate از COUNT_BIG و نوع SUM امن استفاده کنید.
6. خواندن و نوشتن را جداگانه Benchmark کنید.
7. فضا، Log Growth و عملیات نگهداری را ظرفیت‌سنجی کنید.
8. NOEXPAND را تنها با شواهد و تست نسخه به کار ببرید.
9. تعریف و ایندکس‌ها را در Source Control نگه دارید.
10. برای حذف یا تغییر View برنامه Rollback و پایش SLA داشته باشید.

کاربردهای واقعی در پروژه

در سامانه فروش، View می‌تواند قرارداد میان OLTP و گزارش عملیاتی باشد؛ اما گزارش تحلیلی بسیار سنگین شاید به Replica خواندنی یا انبار داده نیاز داشته باشد. انتخاب باید از SLA و الگوی بار شروع شود.

در پروژه مالی، نوع decimal، قواعد NULL، تاریخ مؤثر و Audit اهمیت ویژه دارد. خروجی View باید با داده مرجع تطبیق داده و برای تغییر تعریف، Test مجموع و تعداد ردیف اجرا شود.

در سامانه چندمستاجری، صرف افزودن TenantID به View امنیت کامل نمی‌سازد. Session Context، Row-Level Security، مجوز Role و تست نشت میان Tenantها باید به‌صورت یک طراحی یکپارچه دیده شوند.

برای تحویل حرفه‌ای، Script ساخت و Rollback، تست صحت، Benchmark قبل و بعد، Plan نمونه، ماتریس مجوز و مستند Grain تهیه می‌شود. این بسته نگهداری بعدی و انتقال دانش به تیم عملیات را آسان می‌کند.

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

1. Indexed View چیست؟

Viewی است که پس از ایجاد Unique Clustered Index نتیجه‌اش فیزیکی ذخیره می‌شود و SQL Server آن را همراه با تغییر جداول پایه نگهداری می‌کند.

2. اولین ایندکس روی View چه باید باشد؟

یک Unique Clustered Index روی کلیدی که Grain خروجی را یکتا می‌کند. پس از آن ایجاد Nonclustered Index با ارزیابی هزینه ممکن است.

3. آیا Indexed View برای داشبورد مقرون‌به‌صرفه است؟

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

4. برای بهینه‌سازی Indexed View چه خدماتی لازم است؟

بررسی Plan، SET Options، Query Store، نرخ DML، طراحی کلید، آزمایش NOEXPAND و Benchmark قبل و بعد اجزای اصلی خدمت تخصصی هستند.

5. Indexed View با Materialized View چه تفاوتی دارد؟

در SQL Server اصطلاح عملی Indexed View است و نگهداری آن هم‌زمان با DML انجام می‌شود؛ محصولات دیگر ممکن است Refresh زمان‌بندی‌شده یا On Demand داشته باشند.

6. چگونه پروژه ساخت Indexed View را شروع کنیم؟

Query هدف، SLA، حجم جدول، نرخ نوشتن و نسخه SQL Server را آماده کنید؛ سپس PoC روی داده نماینده بسازید و معیار پذیرش کمی تعریف کنید.

7. چرا CREATE INDEX روی View خطا می‌دهد؟

معمولاً SCHEMABINDING، مالکیت، نام دوبخشی، تابع غیرقطعی، ساختار ممنوع، COUNT_BIG یا SET Options علت است. متن کامل خطا و تعریف View را تطبیق دهید.

8. چرا با وجود Indexed View سرعت بهتر نشد؟

ممکن است Optimizer آن را انتخاب نکرده، Selectivity کم باشد، Query با تعریف تطبیق نکند یا هزینه IO همچنان بالا باشد. Plan با و بدون NOEXPAND مقایسه شود.

9. بهترین روش نگهداری Indexed View چیست؟

پایش استفاده، Statistics، Fragmentation، فضای دیسک، Log و DML ضروری است. ایندکس بدون استفاده باید با داده Query Store بازبینی شود.

10. Indexed View در چه نسخه‌هایی قابل استفاده است؟

قابلیت اصلی در نسخه‌های متعدد SQL Server وجود دارد، اما استفاده خودکار Optimizer و محدودیت‌های دقیق به Version، Edition و Compatibility وابسته است؛ محیط هدف را آزمایش کنید.

سؤالات مصاحبه SQL Server

سؤال 1: این نوع View چه مسئله‌ای را حل می‌کند؟

پاسخ باید هدف منطقی، امنیتی یا کارایی را با توجه به نوع View توضیح دهد و محدودیت‌های آن را نیز بیان کند.

سؤال 2: Optimizer با View چگونه رفتار می‌کند؟

باید تفاوت Expand شدن View معمولی، امکان استفاده از ایندکس View و حذف شاخه در Partitioned View را با Plan توضیح داد.

سؤال 3: چگونه صحت خروجی را آزمایش می‌کنید؟

تعداد ردیف، کلیدهای تکراری، مجموع‌های کنترلی، NULL، مرز تاریخ و مقایسه با Query مرجع باید در تست خودکار پوشش داده شوند.

سؤال 4: چه معیارهایی برای کارایی می‌گیرید؟

Duration، CPU، Logical Reads، Actual Rows، Memory Grant، TempDB، Blocking و برای ساختار مادی‌شده هزینه DML و Log مهم‌اند.

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

وابستگی‌ها استخراج، Migration و Rollback نوشته، قرارداد مصرف تست و انتشار مرحله‌ای با پایش Query Store انجام می‌شود.

سؤال 6: مهم‌ترین نشانه طراحی نامناسب چیست؟

Grain نامشخص، SELECT *، لایه‌های تو در تو، تبدیل ضمنی و نبود Baseline نشانه‌هایی هستند که باید پیش از تولید رفع شوند.

چک‌لیست نهایی

• هدف و مصرف‌کننده View مشخص است.
• Grain و کلید منطقی مستند شده است.
• ستون‌ها و نوع داده صریح هستند.
• وابستگی و Schema Owner کنترل شده‌اند.
• مجوز با اصل حداقل دسترسی تست شده است.
• همه مثال‌ها در محیط آزمایشی اجرا شده‌اند.
• Actual Plan و STATISTICS IO ثبت شده‌اند.
• پارامتر و مرزهای داده آزموده شده‌اند.
• Migration و Rollback در Source Control هستند.
• پایش پس از انتشار و مالک فنی تعیین شده است.

جمع‌بندی

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

پیش از انتشار، صحت ردیف‌ها، مرزهای NULL و تاریخ، مجوز و Regression را کنترل کنید. پس از انتشار نیز Query Store و شاخص‌های عملیاتی را زیر نظر بگیرید تا فرض طراحی در بار واقعی تأیید شود.

برای مرور مقایسه و انتخاب مسیر مناسب به راهنمای جامع Views در SQL Server بازگردید.