آموزش جامع Partitioned Views در SQL Server

مقدمه

Partitioned View چند جدول افقی هم‌ساختار را با UNION ALL زیر یک نام منطقی ترکیب می‌کند. هر جدول عضو بخشی جدا و بدون هم‌پوشانی از دامنه داده را نگهداری می‌کند؛ برای نمونه سفارش‌های هر سال یا هر منطقه در جدول مخصوص قرار می‌گیرند.

CHECK Constraint مرز اعضا را به Optimizer معرفی می‌کند. وقتی Query شرط سازگار روی ستون پارتیشن دارد، SQL Server می‌تواند جدول‌های نامرتبط را از Plan حذف کند. این Partition Elimination عامل اصلی مقیاس‌پذیری خواندن است.

اگر اعضا روی چند SQL Server باشند، الگو Distributed Partitioned View نام می‌گیرد. این روش در سامانه‌های قدیمی برای Scale-out استفاده شده است، اما شبکه، امنیت، تراکنش توزیع‌شده و دسترس‌پذیری پیچیدگی جدی می‌سازند.

در ادامه طراحی محلی و توزیع‌شده، مرز درست تاریخ، Constraint معتبر و پایش IO را بررسی می‌کنیم. برای شناخت دو نوع دیگر، راهنمای مادر Views در SQL Server نقطه شروع مناسبی است.

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

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

CREATE VIEW dbo.PartitionedViewName
    AS
        SELECT Col1, PartitioningColumn, Col3
        FROM dbo.MemberTable_1
        UNION ALL
        SELECT Col1, PartitioningColumn, Col3
        FROM dbo.MemberTable_2;
    GO
    SELECT Col1, Col3
    FROM dbo.PartitionedViewName
    WHERE PartitioningColumn >= @StartBoundary
      AND PartitioningColumn < @EndBoundary;

جزء	نقش فنی	نکته طراحی
نام Schema و View	هویت پایدار شیء	از قرارداد نام‌گذاری تیم پیروی کند
SELECT و ستون‌ها	تعریف Grain و Metadata	ستون‌ها صریح و نوع داده کنترل شود
وابستگی‌های پایه	منبع داده و Plan	Schema، مجوز و ایندکس بررسی شود
Query مصرف‌کننده	فیلتر، Join و Sort نهایی	Plan واقعی با پارامتر نماینده سنجیده شود

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

اعضای Partitioned View باید تعداد و ترتیب ستون سازگار داشته باشند. نوع داده، طول متن، Precision و Scale و در سناریوهای توزیع‌شده Collation را صریح هماهنگ کنید تا تبدیل ضمنی ایجاد نشود.

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

Constraint تنها وقتی برای Optimizer قابل اتکاست که Trusted باشد. بارگذاری با NOCHECK می‌تواند is_not_trusted را یک کند؛ در این حالت باید داده ناسازگار اصلاح و Constraint با WITH CHECK دوباره اعتبارسنجی شود.

UNION ALL بخش جدایی‌ناپذیر الگو است. UNION برای حذف تکرار Sort یا Hash اضافه می‌کند و همچنین قواعد قابل Update بودن و حذف عضو را مختل می‌سازد. نبود هم‌پوشانی باید با Constraint اثبات شود، نه Deduplication.

Predicate مصرف‌کننده باید SARGable و هم‌نوع ستون باشد. YEAR(OrderDate) یا تبدیل ستون می‌تواند Elimination و Seek را ضعیف کند؛ بازه مستقیم تاریخ گزینه شفاف‌تر است.

به‌روزرسانی از طریق Partitioned View محدودیت‌های دقیقی برای کلید، ستون پارتیشن، Trigger، Default و ساختار اعضا دارد. برای عملیات حیاتی، درج مستقیم در عضو صحیح یا Stored Procedure مسیریابی‌شده معمولاً کنترل‌پذیرتر است.

Distributed Partitioned View به نام چهارقسمتی، Linked Server و امنیت Delegation وابسته می‌شود. تراکنش چند عضو ممکن است MSDTC بخواهد و خرابی یک سرور بخشی از نمای منطقی را غیرقابل دسترس کند.

Partitioned View با Partitioned Table یکسان نیست. Partitioned Table یک شیء با Partition Function و Scheme است؛ View چند جدول یا سرور مستقل را ترکیب می‌کند. انتخاب بر اساس عملیات نگهداری، محدودیت نسخه و معماری انجام می‌شود.

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

مثال 1: ساخت دو جدول عضو با مرز سال

پایه Partitioned View مجموعه‌ای از جدول‌های هم‌ساختار با CHECK Constraintهای بدون هم‌پوشانی است. هر جدول فقط داده سال خودش را می‌پذیرد.

CREATE TABLE dbo.Orders_2025
    (
        OrderID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        Amount decimal(18,2) NOT NULL,
        CONSTRAINT PK_Orders_2025 PRIMARY KEY (OrderID, OrderDate),
        CONSTRAINT CK_Orders_2025_Date CHECK
            (OrderDate >= '20250101' AND OrderDate < '20260101')
    );
    CREATE TABLE dbo.Orders_2026
    (
        OrderID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        Amount decimal(18,2) NOT NULL,
        CONSTRAINT PK_Orders_2026 PRIMARY KEY (OrderID, OrderDate),
        CONSTRAINT CK_Orders_2026_Date CHECK
            (OrderDate >= '20260101' AND OrderDate < '20270101')
    );

جدول	محدوده
Orders_2025	2025-01-01 تا قبل از 2026
Orders_2026	2026-01-01 تا قبل از 2027

ستون پارتیشن در کلیدها و محدودیت‌ها اهمیت دارد. ساختار ستون‌ها، ترتیب، نوع داده و Collation اعضا باید برای UNION ALL سازگار باشد.

مثال 2: درج داده نمونه معتبر

داده در جدول عضو متناظر درج می‌شود و CHECK Constraint از ورود تاریخ اشتباه جلوگیری می‌کند. از تاریخ غیرمبهم استفاده شده است.

INSERT dbo.Orders_2025
        (OrderID, OrderDate, CustomerID, Amount)
    VALUES
        (20250001, '20251220', 10, 1500000.00);
    INSERT dbo.Orders_2026
        (OrderID, OrderDate, CustomerID, Amount)
    VALUES
        (20260001, '20260720', 20, 2800000.00);
    SELECT OrderID, OrderDate, Amount FROM dbo.Orders_2026;

OrderID	OrderDate	Amount
20260001	2026-07-20	2800000.00

Constraint را با NOCHECK غیرفعال نکنید. Optimizer برای حذف عضو نامرتبط به Constraint معتبر و Trusted نیاز دارد.

مثال 3: تعریف Partitioned View با UNION ALL

View دو جدول سالانه را زیر یک نام منطقی قرار می‌دهد. UNION ALL برخلاف UNION مرحله حذف تکراری ندارد و برای این الگو ضروری است.

CREATE OR ALTER VIEW dbo.vw_Orders_AllYears
    AS
        SELECT OrderID, OrderDate, CustomerID, Amount
        FROM dbo.Orders_2025
        UNION ALL
        SELECT OrderID, OrderDate, CustomerID, Amount
        FROM dbo.Orders_2026;
    GO
    SELECT OrderID, OrderDate, Amount
    FROM dbo.vw_Orders_AllYears
    ORDER BY OrderDate;

OrderID	OrderDate	Amount
20250001	2025-12-20	1500000.00
20260001	2026-07-20	2800000.00

اعضا باید لیست ستون همسان داشته باشند. ستون‌ها را صریح بنویسید تا تغییر Schema یکی از جداول پنهان نماند.

مثال 4: Partition Elimination با فیلتر بازه‌ای

گزارش تیر ۱۴۰۵ فقط جدول ۲۰۲۶ را لازم دارد. Predicate مستقیم روی OrderDate باید امکان حذف Orders_2025 را در طرح اجرا فراهم کند.

SELECT OrderID, CustomerID, Amount
    FROM dbo.vw_Orders_AllYears
    WHERE OrderDate >= '20260701'
      AND OrderDate < '20260801';

OrderID	CustomerID	Amount
20260001	20	2800000.00

Actual Execution Plan را باز کنید و دسترسی به اعضا را ببینید. تبدیل تابعی روی OrderDate یا Constraint هم‌پوشان می‌تواند Elimination را ضعیف کند.

مثال 5: تجمیع چند سال در SELECT

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

SELECT YEAR(OrderDate) AS SalesYear,
           COUNT_BIG(*) AS OrderCount,
           SUM(Amount) AS TotalAmount
    FROM dbo.vw_Orders_AllYears
    GROUP BY YEAR(OrderDate)
    ORDER BY SalesYear;

SalesYear	OrderCount	TotalAmount
2025	1	1500000.00
2026	1	2800000.00

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

مثال 6: رفتار NULL در ستون اختیاری

اگر نسخه توسعه‌یافته View ستون توضیح Nullable داشته باشد، UNION ALL مقدار NULL را حفظ می‌کند. نوع ستون باید در تمام اعضا یکسان باشد.

SELECT OrderID,
           COALESCE(Description, N'بدون توضیح') AS DescriptionDisplay
    FROM dbo.vw_Orders_WithDescription
    WHERE OrderDate >= '20260101'
      AND OrderDate < '20270101';

OrderID	DescriptionDisplay
20260001	بدون توضیح

برای همسان‌سازی نوع، از CAST صریح استفاده کنید؛ تبدیل ضمنی متفاوت میان اعضا ممکن است کارایی و Metadata خروجی را تغییر دهد.

مثال 7: تشخیص Constraint هم‌پوشان

مرزها باید نیمه‌باز باشند. اگر هر دو جدول تاریخ ۲۰۲۶-۰۱-۰۱ را بپذیرند، Optimizer نمی‌تواند عضو قطعی را تشخیص دهد و درج از طریق View نیز مبهم می‌شود.

SELECT name, definition, is_not_trusted
    FROM sys.check_constraints
    WHERE parent_object_id IN
    (
        OBJECT_ID(N'dbo.Orders_2025'),
        OBJECT_ID(N'dbo.Orders_2026')
    );
    -- مرز درست: >= StartDate AND < NextStartDate

name	is_not_trusted	نکته
CK_Orders_2025_Date	0	مرز پایان انحصاری
CK_Orders_2026_Date	0	مرز شروع شفاف

is_not_trusted باید صفر باشد. برای بازاعتمادسازی پس از پاک‌سازی داده از WITH CHECK CHECK CONSTRAINT استفاده کنید و قبل از آن داده ناسازگار را بیابید.

مثال 8: گزارش سازمانی چند سرور

Distributed Partitioned View می‌تواند اعضا را از Linked Serverها ترکیب کند. Query زیر شکل مفهومی یک View توزیع‌شده را نشان می‌دهد و نیازمند تنظیم امنیت و تراکنش توزیع‌شده است.

CREATE VIEW dbo.vw_GlobalOrders
    AS
        SELECT OrderID, RegionID, OrderDate, Amount
        FROM ServerEast.SalesDb.dbo.Orders_East
        UNION ALL
        SELECT OrderID, RegionID, OrderDate, Amount
        FROM ServerWest.SalesDb.dbo.Orders_West;
    GO
    SELECT RegionID, SUM(Amount) AS TotalAmount
    FROM dbo.vw_GlobalOrders
    WHERE OrderDate >= '20260701'
    GROUP BY RegionID;

RegionID	TotalAmount
1	89000000.00
2	73000000.00

Latency شبکه، دسترس‌پذیری Linked Server، Collation، امنیت Kerberos و MSDTC باید آزمایش شوند. برای معماری جدید گاهی ETL یا Replication انتخاب پایدار‌تری است.

مثال 9: اصلاح Query غیر SARGable

اعمال YEAR روی ستون پارتیشن ممکن است حذف عضوها را دشوار کند. نسخه اصلاح‌شده از دو مرز ثابت استفاده می‌کند.

-- روش ضعیف‌تر:
    SELECT SUM(Amount)
    FROM dbo.vw_Orders_AllYears
    WHERE YEAR(OrderDate) = 2026;
    
    -- روش مناسب‌تر:
    SELECT SUM(Amount)
    FROM dbo.vw_Orders_AllYears
    WHERE OrderDate >= '20260101'
      AND OrderDate < '20270101';

روش	انتظار
YEAR روی ستون	احتمال دسترسی اضافی
بازه مستقیم	امکان Partition Elimination

هر دو Query ممکن است نتیجه یکسان بدهند، اما شکل Predicate در انتخاب طرح مؤثر است. تصمیم را با Actual Plan و STATISTICS IO تأیید کنید.

مثال 10: پایش IO تمام اعضا

این آزمایش نشان می‌دهد Query فیلترشده چند جدول عضو را واقعاً خوانده است. خروجی Messages تعداد Logical Read هر جدول را گزارش می‌کند.

SET STATISTICS IO ON;
    SET STATISTICS TIME ON;
    SELECT OrderID, Amount
    FROM dbo.vw_Orders_AllYears
    WHERE OrderDate >= '20260701'
      AND OrderDate < '20260702';
    SET STATISTICS TIME OFF;
    SET STATISTICS IO OFF;

عضو	نتیجه مطلوب
Orders_2026	دسترسی متناسب با ردیف‌های هدف
Orders_2025	حذف از طرح یا خواندن صفر

آزمون را با پارامتر واقعی، داده نماینده و آمار به‌روز انجام دهید. Parameter Sniffing و نوع پارامتر نیز می‌تواند روی طرح انتخابی اثر بگذارد.

خطاهای رایج

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

CHECK Constraintهای هم‌پوشان یا دارای فاصله ناخواسته
Constraint غیر Trusted پس از بارگذاری با NOCHECK
استفاده از UNION به‌جای UNION ALL
ناهماهنگی نوع، طول یا Collation ستون اعضا
اعمال YEAR یا CAST روی ستون پارتیشن در Predicate
نبود ستون پارتیشن در کلید مناسب عضو
انتظار Elimination بدون فیلتر محدودکننده
نادیده گرفتن Latency و Failure در Linked Server
تراکنش توزیع‌شده بدون پیکربندی و آزمون MSDTC
افزودن عضو سال جدید بدون Update تعریف View و تست Regression

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

با STATISTICS IO بررسی کنید کدام جدول عضو خوانده شده است. اگر Query یک ساله همه سال‌ها را می‌خواند، ابتدا Constraint، Trust و شکل Predicate را بررسی کنید.

Actual Plan می‌تواند شاخه‌های حذف‌شده، Seek Predicate و تبدیل ضمنی را نشان دهد. Plan تخمینی همیشه اثر پارامتر و داده واقعی را کامل آشکار نمی‌کند.

روی هر عضو ایندکس همسان و متناسب با Query ایجاد کنید. تفاوت شدید در Index Design اعضا باعث می‌شود عملکرد یک بازه زمانی با بازه دیگر غیرقابل پیش‌بینی باشد.

Statistics اعضای جدید پس از بارگذاری باید به‌روز شود. عضو تازه با آمار ناکافی می‌تواند تخمین Join و Memory Grant گزارش کلی را منحرف کند.

در حالت توزیع‌شده، Bytes انتقال‌یافته، Remote Query، Round Trip و Timeout را کنار CPU و IO بسنجید. اجرای محلی سریع لزوماً روی شبکه سریع نیست.

افزودن جدول سال جدید را Automation کنید: ساخت Schema، Constraint، ایندکس، مجوز، به‌روزرسانی View و Smoke Test باید یک Deployment اتمی یا دارای Rollback باشد.

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

مرزها را بدون هم‌پوشانی و به‌صورت نیمه‌باز تعریف کنید.
Constraintها را Trusted نگه دارید و وضعیت را پایش کنید.
ساختار و ایندکس اعضا را استاندارد و نسخه‌بندی کنید.
همیشه UNION ALL و فهرست صریح ستون‌ها بنویسید.
Predicate را مستقیم و SARGable روی ستون پارتیشن اعمال کنید.
افزودن عضو جدید را پیش از رسیدن مرز زمانی انجام دهید.
Elimination را با Actual Plan و STATISTICS IO اثبات کنید.
سناریوی خرابی عضو Remote را در معماری توزیع‌شده تمرین کنید.
امنیت Linked Server و حداقل دسترسی را بازبینی کنید.
Partitioned Table و راهکار ETL را نیز پیش از انتخاب مقایسه کنید.

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

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

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

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

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

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

1. Partitioned View چیست؟

Viewی مبتنی بر UNION ALL است که چند جدول افقی هم‌ساختار با دامنه‌های مجزا را یک منبع منطقی نشان می‌دهد.

2. Partition Elimination چگونه رخ می‌دهد؟

CHECK Constraint معتبر مرز عضو را اعلام می‌کند و Predicate مستقیم روی ستون پارتیشن به Optimizer اجازه می‌دهد شاخه‌های نامرتبط را حذف کند.

3. آیا Partitioned View برای آرشیو سالانه اقتصادی است؟

برای جداول مستقل سالانه و نگهداری جدا می‌تواند مناسب باشد، ولی هزینه Deployment، Query چندسال و مدیریت Constraint باید با Partitioned Table مقایسه شود.

4. خدمت طراحی Distributed Partitioned View شامل چیست؟

تحلیل Topology، Linked Server، امنیت، MSDTC، Latency، Failure Mode، Index و Benchmark Remote بخش‌های ضروری یک طراحی حرفه‌ای هستند.

5. Partitioned View بهتر است یا Partitioned Table؟

Table یک شیء واحد و مدیریت پارتیشن داخلی دارد؛ View چند جدول یا سرور را ترکیب می‌کند. محدودیت نسخه، Scale-out و عملیات نگهداری انتخاب را تعیین می‌کند.

6. چگونه پروژه مهاجرت جدول‌های سالانه را اجرا کنیم؟

Schema مشترک، مرزها، پاک‌سازی داده، Constraint، ایندکس، View، تست نتیجه و Plan و Rollback باید در طرح مهاجرت مرحله‌بندی شوند.

7. چرا همه جدول‌های عضو خوانده می‌شوند؟

Constraint هم‌پوشان یا غیر Trusted، Predicate غیر SARGable، تبدیل ضمنی یا نبود فیلتر محدودکننده علت‌های رایج‌اند. Plan و IO را بررسی کنید.

8. چگونه عملکرد Query چند ساله را بهتر کنیم؟

فیلتر لازم، ایندکس همسان اعضا، Statistics به‌روز، ستون‌های محدود و Aggregate مناسب کمک می‌کند. گزارش واقعاً چندسالۀ بزرگ ممکن است به انبار داده نیاز داشته باشد.

9. بهترین روش افزودن سال جدید چیست؟

جدول عضو را پیشاپیش با Constraint و ایندکس استاندارد بسازید، View را در Deployment کنترل‌شده تغییر دهید و Elimination و مجوز را Smoke Test کنید.

10. Partitioned View در نسخه‌های جدید پشتیبانی می‌شود؟

الگو همچنان شناخته‌شده است، اما قواعد قابل Update بودن و Distributed Query به نسخه و تنظیمات وابسته‌اند. مستندات نسخه هدف و تست محیطی ملاک نهایی است.

سؤالات مصاحبه 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 بازگردید.

Partitioned Views در SQL Server؛ طراحی UNION ALL با ۱۰ مثال