مقدمه و هدف راهنما

ایندکس یکی از مؤثرترین و در عین حال پرهزینه‌ترین ابزارهای تنظیم Performance در SQL Server است. یک طراحی درست می‌تواند گزارش چنددقیقه‌ای را به پاسخ چندثانیه‌ای تبدیل کند، اما ایندکس اشتباه ممکن است فقط دیسک، حافظه، Transaction Log و زمان عملیات نوشتن را مصرف کند. بنابراین دستور CREATE INDEX باید بخشی از یک فرایند اندازه‌گیری‌شده باشد، نه واکنشی عجولانه به هر Query کند.

این مقاله مادر همه دستورهای مجموعه ساخت ایندکس را از یک زاویه واحد کنار هم قرار می‌دهد: چه مسئله‌ای را حل می‌کنند، چه پیش‌نیازی دارند، چه هزینه‌ای ایجاد می‌کنند و چه زمانی باید سراغ مقاله تخصصی آن‌ها رفت. از Rowstore کلاسیک تا Columnstore تحلیلی و ایندکس‌های XML، Spatial و Full-Text، هر ساختار برای نوع متفاوتی از داده و بار کاری ساخته شده است.

در طراحی حرفه‌ای ابتدا Workload مشاهده می‌شود. Query Store، Actual Execution Plan، آمار IO، زمان CPU و تعداد اجرای Query مشخص می‌کنند مشکل اصلی کجاست. سپس ساختار موجود جدول، کلید خوشه‌ای، ایندکس‌های هم‌پوشان، توزیع داده و نرخ تغییر بررسی می‌شوند. فقط پس از این مرحله می‌توان تعریف قابل دفاعی برای ایندکس پیشنهاد داد.

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

ایندکس خوب کمکی به یک Query واقعی است؛ ایندکس بد فقط یک ساختار اضافی است که در هر تغییر داده باید دوباره نگهداری شود.

دسترسی سریع به مقاله‌های تخصصی

هر لینک زیر به آموزش مستقل همان دستور می‌رود. مسیرها Root-relative هستند و با تغییر دامنه سایت شکسته نمی‌شوند.

• CREATE INDEX — ساخت ایندکس استاندارد
• CREATE CLUSTERED INDEX — ساخت ایندکس خوشه‌ای
• CREATE NONCLUSTERED INDEX — ساخت ایندکس غیرخوشه‌ای
• CREATE UNIQUE INDEX — ساخت ایندکس یکتا
• CREATE COLUMNSTORE INDEX — ساخت ایندکس ستونی
• CREATE CLUSTERED COLUMNSTORE INDEX — ساخت ایندکس ستونی خوشه‌ای
• CREATE NONCLUSTERED COLUMNSTORE INDEX — ساخت ایندکس ستونی غیرخوشه‌ای
• CREATE INDEX ... WHERE — ساخت ایندکس فیلترشده
• CREATE INDEX ... INCLUDE — ساخت ایندکس با ستون‌های INCLUDE
• CREATE XML INDEX — ساخت ایندکس XML
• CREATE SPATIAL INDEX — ساخت ایندکس مکانی
• CREATE FULLTEXT INDEX — ساخت ایندکس جست‌وجوی تمام‌متن
• CREATE INDEX ... WITH (DROP_EXISTING = ON) — بازسازی یا تغییر ایندکس با DROP_EXISTING

معماری ایندکس و هزینه واقعی آن

ایندکس Rowstore معمولاً ساختاری درختی دارد. صفحات ریشه و میانی مسیر رسیدن به صفحه برگ را کوتاه می‌کنند. در ایندکس Clustered، سطح برگ همان صفحات داده است؛ در Nonclustered، برگ شامل کلید و Row Locator است و گاهی برای ستون‌های دیگر Key Lookup انجام می‌شود. عرض کلید، تعداد سطح‌ها و Selectivity بر هزینه دسترسی اثر می‌گذارند.

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

Nonclustered Index یک کپی کامل از جدول نیست، ولی کلیدها، Row Locator و ستون‌های INCLUDE فضای قابل توجهی مصرف می‌کنند. اگر Query فقط ستون‌های داخل ساختار را نیاز داشته باشد، ایندکس Covering می‌تواند Lookup را حذف کند. با این حال افزودن بی‌ضابطه INCLUDE، اندازه برگ و هزینه نگهداری را بالا می‌برد.

Filtered Index فقط زیرمجموعه‌ای از ردیف‌ها را نگه می‌دارد و برای داده فعال، مقدار غیرNULL یا وضعیت خاص بسیار مفید است. مزیت آن اندازه کمتر و Statistics متمرکزتر است، اما Predicate Query باید با شرط فیلتر هم‌خوان باشد. تبدیل ضمنی یا Parameterization نامناسب ممکن است استفاده از آن را محدود کند.

Columnstore داده‌ها را به شکل ستونی و فشرده سازمان‌دهی می‌کند. خواندن چند ستون از میلیون‌ها ردیف، تجمیع و Batch Mode معمولاً نقاط قوت آن هستند. Clustered Columnstore کل جدول را ستونی می‌کند؛ Nonclustered Columnstore یک کپی تحلیلی از ستون‌های منتخب روی جدول Rowstore فراهم می‌کند و برای تحلیل نزدیک به بلادرنگ کاربرد دارد.

XML Index نمایش خردشده و پایدار محتوای XML را برای متدهای XQuery فراهم می‌کند. Spatial Index فضای دوبعدی را برای کاهش اشیای نامزد در عملیات فاصله و تقاطع تقسیم می‌کند. Full-Text Index نیز موتور واژه‌شناختی، زبان، Stoplist و Population خود را دارد و جایگزین ساده LIKE نیست.

هزینه واقعی ایندکس فقط حجم فایل نیست. هر INSERT، DELETE و بسیاری از UPDATEها باید ساختارهای مرتبط را تغییر دهند. این کار Log، قفل، CPU، Cache و مدت Backup یا Restore را تحت تأثیر قرار می‌دهد. در سیستم پرتراکنش، یک ایندکس کم‌مصرف ممکن است بیشتر از منفعتش هزینه ایجاد کند.

آمار و Cardinality Estimation حلقه اتصال Query و ایندکس هستند. حتی تعریف مناسب با Statistics قدیمی یا پارامتر نامتعارف ممکن است Plan ضعیفی تولید کند. بنابراین ساخت ایندکس باید همراه با سیاست آمار، Query Store و پایش Regression باشد.

جدول مقایسه دستورهای ساخت ایندکس

CREATE INDEX در SQL Server

CREATE INDEX برای ایجاد یک ایندکس B-tree استاندارد برای سریع‌تر شدن جست‌وجو، مرتب‌سازی و اتصال جدول‌ها استفاده می‌شود. این دستور در گروه ایندکس‌های Rowstore قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

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

آموزش کامل CREATE INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE CLUSTERED INDEX در SQL Server

CREATE CLUSTERED INDEX برای تعیین ساختار فیزیکی ردیف‌های جدول بر پایه کلید خوشه‌ای و حذف هزینه جست‌وجوی اضافی در سناریوهای مناسب استفاده می‌شود. این دستور در گروه ایندکس‌های Rowstore قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن انتخاب کلیدی باریک، پایدار، یکتا یا نزدیک به یکتا و ترجیحاً افزایشی است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE CLUSTERED INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE NONCLUSTERED INDEX در SQL Server

CREATE NONCLUSTERED INDEX برای ساخت مسیر دسترسی جدا از داده اصلی برای پوشش Predicateها، Joinها و مرتب‌سازی‌های پرتکرار استفاده می‌شود. این دستور در گروه ایندکس‌های Rowstore قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن جلوگیری از هم‌پوشانی بی‌فایده ایندکس‌ها و رشد بیش از حد فضای ذخیره‌سازی است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE NONCLUSTERED INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE UNIQUE INDEX در SQL Server

CREATE UNIQUE INDEX برای تضمین یکتایی مقدار یا ترکیب ستون‌ها همراه با فراهم‌کردن مسیر دسترسی سریع استفاده می‌شود. این دستور در گروه یکپارچگی و Rowstore قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن پاک‌سازی داده‌های تکراری و تصمیم آگاهانه درباره رفتار NULL پیش از ایجاد ایندکس است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE UNIQUE INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE COLUMNSTORE INDEX در SQL Server

CREATE COLUMNSTORE INDEX برای فشرده‌سازی ستونی و اجرای Batch Mode برای اسکن و تجمیع حجم زیاد داده تحلیلی استفاده می‌شود. این دستور در گروه Columnstore و تحلیل قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن سنجش اندازه جدول، الگوی بارگذاری و تفاوت بار تحلیلی با تراکنش‌های تک‌ردیفی است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE COLUMNSTORE INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE CLUSTERED COLUMNSTORE INDEX در SQL Server

CREATE CLUSTERED COLUMNSTORE INDEX برای ذخیره کل جدول به شکل Columnstore برای انبار داده، Fact Table و گزارش‌های تجمیعی بزرگ استفاده می‌شود. این دستور در گروه Columnstore و انبار داده قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن مدیریت Rowgroup، کیفیت فشرده‌سازی، پارتیشن‌بندی و الگوی بارگذاری دسته‌ای است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE CLUSTERED COLUMNSTORE INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE NONCLUSTERED COLUMNSTORE INDEX در SQL Server

CREATE NONCLUSTERED COLUMNSTORE INDEX برای افزودن نمای ستونی تحلیلی روی جدول Rowstore و ترکیب OLTP با گزارش‌گیری نزدیک به بلادرنگ استفاده می‌شود. این دستور در گروه تحلیل بلادرنگ قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن انتخاب ستون‌های تحلیلی، کنترل فضای کپی ستونی و اثر نگهداری بر تراکنش‌ها است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE NONCLUSTERED COLUMNSTORE INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE INDEX ... WHERE در SQL Server

CREATE INDEX ... WHERE برای ایندکس‌کردن فقط زیرمجموعه‌ای از ردیف‌ها مانند سفارش‌های باز، مقادیر غیرNULL یا رکوردهای فعال استفاده می‌شود. این دستور در گروه ایندکس‌های هدفمند قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن هم‌خوانی دقیق Predicate کوئری با شرط فیلتر و مدیریت Parameterization است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE INDEX ... WHERE با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE INDEX ... INCLUDE در SQL Server

CREATE INDEX ... INCLUDE برای پوشش کامل خروجی کوئری بدون بزرگ‌کردن کلید ایندکس و کاهش Key Lookup استفاده می‌شود. این دستور در گروه ایندکس‌های پوشاننده قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن قرار دادن ستون‌های جست‌وجو در Key و ستون‌های صرفاً خروجی در INCLUDE است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE INDEX ... INCLUDE با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE XML INDEX در SQL Server

CREATE XML INDEX برای ایجاد نمایش پایدار از ساختار XML برای شتاب‌دادن به متدهای exist، value، nodes و query استفاده می‌شود. این دستور در گروه داده‌های نیمه‌ساخت‌یافته قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن وجود کلید خوشه‌ای روی جدول، انتخاب Primary یا Secondary XML Index و هزینه فضای ذخیره‌سازی است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE XML INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE SPATIAL INDEX در SQL Server

CREATE SPATIAL INDEX برای کاهش تعداد اشیای geometry یا geography بررسی‌شده در پرس‌وجوهای فاصله، تقاطع و محدوده استفاده می‌شود. این دستور در گروه داده‌های مکانی قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن انتخاب نوع داده و SRID صحیح، Bounding Box مناسب برای geometry و آزمون با داده واقعی است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE SPATIAL INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE FULLTEXT INDEX در SQL Server

CREATE FULLTEXT INDEX برای جست‌وجوی زبانی و واژه‌محور با CONTAINS و FREETEXT در متن‌های طولانی استفاده می‌شود. این دستور در گروه جست‌وجوی متنی قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن نصب مؤلفه Full-Text، کلید یکتای معتبر، زبان ستون، Stoplist و برنامه Populate است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE FULLTEXT INDEX با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

CREATE INDEX ... WITH (DROP_EXISTING = ON) در SQL Server

CREATE INDEX ... WITH (DROP_EXISTING = ON) برای جایگزینی تعریف ایندکس موجود در یک عملیات کنترل‌شده برای تغییر کلید، INCLUDE یا نوع ساختار استفاده می‌شود. این دستور در گروه نگهداری و مهاجرت ایندکس قرار می‌گیرد و باید بر اساس Query، حجم داده و الگوی تغییر انتخاب شود.

مهم‌ترین نکته طراحی آن برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی است. پیش از استقرار، Plan و IO مبنا، فضای موردنیاز، زمان ساخت و برنامه بازگشت را ثبت کنید تا نتیجه قابل اندازه‌گیری و قابل برگشت باشد.

آموزش کامل CREATE INDEX ... WITH (DROP_EXISTING = ON) با ده مثال عملی شامل Syntax، خروجی نمونه، خطاهای رایج، Performance، FAQ و چک‌لیست اختصاصی است.

فرایند انتخاب ایندکس مناسب

گام نخست تعریف مسئله کسب‌وکار است. به جای جمله کلی «سیستم کند است»، Query، صفحه یا گزارش، زمان مورد انتظار، تعداد کاربران و بازه داده را مشخص کنید. این تعریف از بهینه‌سازی بخشی که اثر واقعی ندارد جلوگیری می‌کند.

1. Queryهای پرتکرار و پرهزینه را از Query Store یا مانیتورینگ استخراج کنید.
2. Actual Execution Plan، پارامترها، Logical Reads، CPU و مدت اجرا را ثبت کنید.
3. Schema، کلید خوشه‌ای، ایندکس‌های موجود و Statistics را بررسی کنید.
4. نوع دسترسی را تشخیص دهید: OLTP نقطه‌ای، Range، گزارش تجمیعی، متن، XML یا مکان.
5. کوچک‌ترین تعریف ایندکس که Queryهای مهم را پوشش می‌دهد طراحی کنید.
6. اثر روی INSERT، UPDATE، DELETE، Log، Replication و Backup را برآورد کنید.
7. اسکریپت را با حجم مشابه تولید در Staging اجرا و زمان‌گیری کنید.
8. برنامه Online یا پنجره نگهداری، معیار توقف و Rollback را تعیین کنید.
9. پس از استقرار، Plan و شاخص‌ها را در بار واقعی دوباره اندازه بگیرید.
10. تاریخ بازبینی و مالک ایندکس را ثبت کنید تا ساختار بدون استفاده باقی نماند.

اگر Query عمدتاً چند ردیف مشخص را با Equality یا Range می‌خواند، Rowstore نقطه شروع مناسبی است. اگر فقط درصد کمی از ردیف‌ها هدف است، Filtered را بررسی کنید. اگر خروجی چند ستون باعث Lookup می‌شود، INCLUDE می‌تواند مفید باشد. برای Scan و Aggregate بزرگ، Columnstore را آزمایش کنید. داده تخصصی نیز ساختار تخصصی خود را می‌طلبد.

Missing Index Suggestion فقط یک سرنخ است. این پیشنهادها هزینه نگهداری، هم‌پوشانی با ایندکس‌های دیگر و گاهی ترتیب بهینه ستون‌ها را کامل در نظر نمی‌گیرند. چند پیشنهاد مشابه را می‌توان پس از تحلیل در یک تعریف هدفمند ادغام کرد.

شش مثال جامع و مستقل

مثال جامع 1: ساخت پایه روی ستون پرتکرار با CREATE INDEX

یک مسیر دسترسی اولیه برای جست‌وجوی پرتکرار ساخته می‌شود و سپس متادیتای آن کنترل می‌گردد. در این مثال، هدف عملی ایجاد یک ایندکس B-tree استاندارد برای سریع‌تر شدن جست‌وجو، مرتب‌سازی و اتصال جدول‌ها است.

IF OBJECT_ID(N'dbo.DemoCreateIndexE1', N'U') IS NOT NULL
        DROP TABLE dbo.DemoCreateIndexE1;
    
    CREATE TABLE dbo.DemoCreateIndexE1
    (
        RowID       int IDENTITY(1,1) NOT NULL,
        CustomerID  int NOT NULL,
        ExternalCode varchar(30) NULL,
        OrderDate   date NOT NULL,
        Status      tinyint NULL,
        Amount      decimal(12,2) NOT NULL,
        Description nvarchar(200) NULL
    );
    
    INSERT INTO dbo.DemoCreateIndexE1
        (CustomerID, ExternalCode, OrderDate, Status, Amount, Description)
    VALUES
        (101, 'A-1001', '2026-07-01', 1, 125000.00, N'سفارش فعال'),
        (102, 'A-1002', '2026-07-12', 2,  84000.00, N'سفارش بسته'),
        (101, 'A-1003', '2026-07-20', NULL, 99000.00, N'در انتظار بررسی');
    
    CREATE INDEX IX_DemoCreateIndex_E1
        ON dbo.DemoCreateIndexE1(CustomerID);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoCreateIndexE1')
      AND name = N'IX_DemoCreateIndex_E1';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoCreateIndexE1
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

تحلیل نتیجه: این الگو نقطه شروع است؛ در سامانه واقعی باید انتخاب ستون با Execution Plan و آمار مصرف تأیید شود. برای ساخت ایندکس استاندارد همچنین باید انتخاب ترتیب ستون‌های کلیدی و کنترل هزینه نگهداری در عملیات درج و ویرایش را در بازبینی نهایی ثبت کرد.

مثال جامع 2: کلید مرکب برای جست‌وجوی چندشرطی با CREATE INDEX ... WHERE

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

IF OBJECT_ID(N'dbo.DemoFilteredIndexE2', N'U') IS NOT NULL
        DROP TABLE dbo.DemoFilteredIndexE2;
    
    CREATE TABLE dbo.DemoFilteredIndexE2
    (
        RowID       int IDENTITY(1,1) NOT NULL,
        CustomerID  int NOT NULL,
        ExternalCode varchar(30) NULL,
        OrderDate   date NOT NULL,
        Status      tinyint NULL,
        Amount      decimal(12,2) NOT NULL,
        Description nvarchar(200) NULL
    );
    
    INSERT INTO dbo.DemoFilteredIndexE2
        (CustomerID, ExternalCode, OrderDate, Status, Amount, Description)
    VALUES
        (101, 'A-1001', '2026-07-01', 1, 125000.00, N'سفارش فعال'),
        (102, 'A-1002', '2026-07-12', 2,  84000.00, N'سفارش بسته'),
        (101, 'A-1003', '2026-07-20', NULL, 99000.00, N'در انتظار بررسی');
    
    CREATE INDEX IX_DemoFilteredIndex_E2
        ON dbo.DemoFilteredIndexE2(CustomerID, OrderDate) WHERE Status = 1;
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoFilteredIndexE2')
      AND name = N'IX_DemoFilteredIndex_E2';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoFilteredIndexE2
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

تحلیل نتیجه: ستون سمت چپ کلید نقش تعیین‌کننده دارد و تغییر ترتیب ستون‌ها می‌تواند شکل Seek را عوض کند. برای ساخت ایندکس فیلترشده همچنین باید هم‌خوانی دقیق Predicate کوئری با شرط فیلتر و مدیریت Parameterization را در بازبینی نهایی ثبت کرد.

مثال جامع 3: پشتیبانی از مرتب‌سازی نزولی با CREATE CLUSTERED COLUMNSTORE INDEX

تعریف ایندکس با ترتیب نزولی برای گزارش‌هایی بررسی می‌شود که تازه‌ترین داده‌ها را در ابتدای نتیجه می‌خواهند. در این مثال، هدف عملی ذخیره کل جدول به شکل Columnstore برای انبار داده، Fact Table و گزارش‌های تجمیعی بزرگ است.

IF OBJECT_ID(N'dbo.DemoClusteredColumnstoreE3', N'U') IS NOT NULL
        DROP TABLE dbo.DemoClusteredColumnstoreE3;
    
    CREATE TABLE dbo.DemoClusteredColumnstoreE3
    (
        SaleID      bigint IDENTITY(1,1) NOT NULL,
        CustomerID int NOT NULL,
        ProductID  int NOT NULL,
        SaleDate   date NOT NULL,
        Quantity   int NOT NULL,
        NetAmount  decimal(14,2) NOT NULL,
        RegionID   smallint NULL
    );
    
    INSERT INTO dbo.DemoClusteredColumnstoreE3
        (CustomerID, ProductID, SaleDate, Quantity, NetAmount, RegionID)
    VALUES
        (101, 10, '2026-07-01', 2, 150000.00, 1),
        (102, 11, '2026-07-10', 1,  87000.00, 2),
        (103, 10, '2026-07-20', 5, 390000.00, NULL);
    
    CREATE CLUSTERED COLUMNSTORE INDEX IX_DemoClusteredColumnstore_E3
        ON dbo.DemoClusteredColumnstoreE3;
    
    SELECT name, type_desc, is_disabled
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoClusteredColumnstoreE3');
    
    SELECT ProductID, SUM(NetAmount) AS TotalAmount
    FROM dbo.DemoClusteredColumnstoreE3
    GROUP BY ProductID;
    

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

مثال جامع 4: استفاده در شرط و اتصال جدول‌ها با CREATE XML INDEX

نمونه‌ای نزدیک به گزارش عملی ساخته می‌شود تا نقش ایندکس در Predicate و Join یا بازیابی ردیف‌های هدف دیده شود. در این مثال، هدف عملی ایجاد نمایش پایدار از ساختار XML برای شتاب‌دادن به متدهای exist، value، nodes و query است.

IF OBJECT_ID(N'dbo.DemoXmlIndexE4', N'U') IS NOT NULL
        DROP TABLE dbo.DemoXmlIndexE4;
    
    CREATE TABLE dbo.DemoXmlIndexE4
    (
        DocumentID int NOT NULL,
        Payload xml NOT NULL,
        CONSTRAINT PK_DemoXmlIndexE4 PRIMARY KEY CLUSTERED (DocumentID)
    );
    
    INSERT INTO dbo.DemoXmlIndexE4(DocumentID, Payload)
    VALUES
        (1, N'<order id="1001"><customer>Ali</customer><amount>125000</amount></order>'),
        (2, N'<order id="1002"><customer>Sara</customer><amount>84000</amount></order>');
    
    CREATE PRIMARY XML INDEX PXML_DemoXmlIndexE4
        ON dbo.DemoXmlIndexE4(Payload);
    
    CREATE XML INDEX IX_DemoXmlIndex_E4
        ON dbo.DemoXmlIndexE4(Payload)
        USING XML INDEX PXML_DemoXmlIndexE4 FOR PROPERTY;
    
    SELECT name, secondary_type_desc
    FROM sys.xml_indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoXmlIndexE4');
    
    SELECT DocumentID
    FROM dbo.DemoXmlIndexE4
    WHERE Payload.exist('/order[@id="1001"]') = 1;
    

تحلیل نتیجه: وجود ایندکس تضمین استفاده از آن نیست؛ Optimizer بر پایه آمار، تعداد ردیف و هزینه تخمینی تصمیم می‌گیرد. برای ساخت ایندکس XML همچنین باید وجود کلید خوشه‌ای روی جدول، انتخاب Primary یا Secondary XML Index و هزینه فضای ذخیره‌سازی را در بازبینی نهایی ثبت کرد.

مثال جامع 5: تنظیم گزینه‌های ساخت و نگهداری با CREATE SPATIAL INDEX

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

IF OBJECT_ID(N'dbo.DemoSpatialIndexE5', N'U') IS NOT NULL
        DROP TABLE dbo.DemoSpatialIndexE5;
    
    CREATE TABLE dbo.DemoSpatialIndexE5
    (
        PlaceID int NOT NULL,
        PlaceName nvarchar(100) NOT NULL,
        Location geography NOT NULL,
        CONSTRAINT PK_DemoSpatialIndexE5 PRIMARY KEY CLUSTERED (PlaceID)
    );
    
    INSERT INTO dbo.DemoSpatialIndexE5(PlaceID, PlaceName, Location)
    VALUES
        (1, N'تهران', geography::Point(35.6892, 51.3890, 4326)),
        (2, N'اصفهان', geography::Point(32.6546, 51.6680, 4326));
    
    CREATE SPATIAL INDEX IX_DemoSpatialIndex_E5
        ON dbo.DemoSpatialIndexE5(Location)
        USING GEOGRAPHY_AUTO_GRID
        WITH (CELLS_PER_OBJECT = 16);
    
    SELECT name, type_desc
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoSpatialIndexE5');
    

تحلیل نتیجه: هر گزینه هزینه و پیش‌نیاز خود را دارد؛ مقدار مناسب باید با نسخه SQL Server، Edition و الگوی بار واقعی آزموده شود. برای ساخت ایندکس مکانی همچنین باید انتخاب نوع داده و SRID صحیح، Bounding Box مناسب برای geometry و آزمون با داده واقعی را در بازبینی نهایی ثبت کرد.

مثال جامع 6: رفتار با NULL و داده‌های اختیاری با CREATE FULLTEXT INDEX

داده‌ای دارای مقدار NULL وارد می‌شود تا اثر آن بر تعریف، انتخاب‌پذیری و نتیجه Query روشن شود. در این مثال، هدف عملی جست‌وجوی زبانی و واژه‌محور با CONTAINS و FREETEXT در متن‌های طولانی است.

IF OBJECT_ID(N'dbo.DemoFullTextIndexE6', N'U') IS NOT NULL
        DROP TABLE dbo.DemoFullTextIndexE6;
    
    IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = N'FTC_Perf12')
        CREATE FULLTEXT CATALOG FTC_Perf12 AS DEFAULT;
    
    CREATE TABLE dbo.DemoFullTextIndexE6
    (
        ArticleID int NOT NULL,
        Title nvarchar(200) NOT NULL,
        Body nvarchar(max) NOT NULL
    );
    
    CREATE UNIQUE INDEX UX_DemoFullTextIndexE6_ArticleID
        ON dbo.DemoFullTextIndexE6(ArticleID);
    
    INSERT INTO dbo.DemoFullTextIndexE6(ArticleID, Title, Body)
    VALUES
        (1, N'آموزش SQL Server', N'ایندکس مناسب سرعت جست‌وجو را افزایش می‌دهد.'),
        (2, N'بهینه‌سازی پایگاه داده', N'طرح اجرا و آمار ورودی تصمیم مهمی هستند.');
    
    CREATE FULLTEXT INDEX ON dbo.DemoFullTextIndexE6
    (
        Title LANGUAGE 1065,
        Body LANGUAGE 1065
    )
    KEY INDEX UX_DemoFullTextIndexE6_ArticleID
    ON FTC_Perf12
    WITH CHANGE_TRACKING MANUAL, STOPLIST = OFF;
    
    SELECT object_id, change_tracking_state_desc
    FROM sys.fulltext_indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoFullTextIndexE6');
    

تحلیل نتیجه: NULL مقدار عادی نیست و قواعد یکتایی، فیلتر و برآورد Cardinality را باید آگاهانه بررسی کرد. برای ساخت ایندکس جست‌وجوی تمام‌متن همچنین باید نصب مؤلفه Full-Text، کلید یکتای معتبر، زبان ستون، Stoplist و برنامه Populate را در بازبینی نهایی ثبت کرد.

خطاهای طراحی که باید پیشگیری شوند

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

انتخاب کلید بسیار عریض یا متغیر برای Clustered Index، Row Locator همه Nonclusteredها را بزرگ می‌کند. کلید تصادفی می‌تواند Page Split و Fragmentation را افزایش دهد. در مقابل، کلید افزایشی بسیار پرترافیک ممکن است در بار هم‌زمان نقطه داغ ایجاد کند. پاسخ درست با الگوی درج و نسخه موتور مرتبط است.

استفاده از تابع روی ستون، تفاوت Collation یا تبدیل ضمنی میان نوع پارامتر و ستون می‌تواند Seek را از بین ببرد. در چنین شرایطی افزودن ایندکس جدید درمان ریشه‌ای نیست. ابتدا Query را SARGable و نوع داده را هماهن کنید.

ساخت ایندکس بزرگ بدون ظرفیت کافی برای Log، TempDB و Data File ممکن است عملیات را متوقف کند. Online بودن نیز به معنای بدون‌هزینه بودن نیست. قفل‌های کوتاه ابتدا و انتها، مصرف CPU و فشار IO باید در برنامه نگهداری لحاظ شوند.

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

پایش پس از ایجاد ایندکس

پس از استقرار، Queryهای هدف را با پارامترهای معمول و مرزی اجرا کنید. Actual Plan باید مسیر دسترسی و تعداد واقعی ردیف‌ها را نشان دهد. کاهش Logical Reads اغلب علامت خوبی است، ولی افزایش CPU، Memory Grant یا Blocking می‌تواند منفعت را خنثی کند.

sys.dm_db_index_usage_stats دیدی از Seek، Scan، Lookup و Update می‌دهد. sys.dm_db_index_operational_stats برای قفل، Latch و عملیات فیزیکی مفید است. برای Columnstore، نماهای Rowgroup و برای Full-Text یا XML نماهای تخصصی نیز باید بررسی شوند.

Query Store تغییر Plan و زمان اجرا را در طول زمان ثبت می‌کند. خط مبنا را نگه دارید تا Regression پس از رشد داده، به‌روزرسانی Statistics یا ارتقای نسخه شناسایی شود. هدف، بهبود پایدار است نه موفقیت لحظه‌ای یک Benchmark.

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

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

سؤال 1: ایندکس در SQL Server چیست؟

ایندکس یک ساختار فیزیکی کمکی برای دسترسی سریع‌تر یا پردازش کارآمدتر داده است. Rowstore معمولاً از B-tree استفاده می‌کند، Columnstore داده را ستونی نگه می‌دارد و ساختارهای XML، Spatial و Full-Text برای نوع خاصی از Query طراحی شده‌اند.

سؤال 2: آیا هر ستون پرتکرار باید ایندکس داشته باشد؟

خیر. تعداد اجرای Query، Selectivity، ترتیب شرط‌ها، حجم جدول، هزینه نگهداری و ایندکس‌های موجود باید با هم بررسی شوند. افزودن ایندکس‌های متعدد ممکن است خواندن را کمی بهتر ولی عملیات نوشتن، Log، Backup و فضای دیسک را به‌طور محسوسی سنگین کند.

سؤال 3: برای شروع بهینه‌سازی کدام ایندکس مناسب‌تر است؟

از نوع ایندکس شروع نکنید؛ از Query پرهزینه و هدف کسب‌وکار شروع کنید. Actual Execution Plan، STATISTICS IO، Query Store و توزیع داده مشخص می‌کنند یک Nonclustered، Filtered، Covering، Columnstore یا تغییر Query راه مناسب‌تری است.

سؤال 4: هزینه طراحی حرفه‌ای ایندکس چگونه تعیین می‌شود؟

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

سؤال 5: تفاوت Clustered و Nonclustered چیست؟

Clustered ساختار اصلی ردیف‌های Rowstore را تعیین می‌کند و هر جدول فقط یک ساختار خوشه‌ای دارد. Nonclustered مسیر جداگانه‌ای با کلید و Row Locator می‌سازد و می‌توان چند مورد هدفمند داشت. انتخاب میان آن‌ها به الگوی دسترسی و کلید مناسب بستگی دارد.

سؤال 6: آیا می‌توان ساخت ایندکس را به تیم مشاوره سپرد؟

بله، ولی دسترسی کنترل‌شده به Schema، Queryها، Plan و شاخص‌های عملکرد لازم است. تیم مجری باید تغییر را در Staging آزمایش کند، زمان و فضای موردنیاز را تخمین بزند و اجرای تولید را با مانیتورینگ و برنامه Rollback انجام دهد.

سؤال 7: چرا CREATE INDEX با خطا مواجه می‌شود؟

نام تکراری، ستون نامعتبر، داده تکراری برای Unique، نبود کلید لازم برای XML یا Full-Text، نوع داده ناسازگار، طول بیش از حد کلید و گزینه پشتیبانی‌نشده دلایل رایج‌اند. متن کامل خطا و نسخه سرور مسیر عیب‌یابی را مشخص می‌کند.

سؤال 8: چگونه اثر ایندکس بر Performance سنجیده می‌شود؟

Query و پارامتر ثابت را قبل و بعد اجرا کنید و Logical Reads، CPU، زمان، Plan، Memory Grant و Waitها را مقایسه کنید. اثر تجمعی در بار هم‌زمان و هزینه INSERT، UPDATE و DELETE نیز باید سنجیده شود؛ یک اجرای سریع به‌تنهایی کافی نیست.

سؤال 9: بهترین روش جلوگیری از ایندکس‌های اضافی چیست؟

پیش از ساخت، sys.indexes، Usage Stats و تعریف ستون‌ها را مقایسه کنید. ایندکس‌های هم‌پوشان گاهی قابل ادغام‌اند، اما حذف باید پس از دوره پایش و بررسی همه Workloadها انجام شود. نام مستند و تاریخ بازبینی به کنترل چرخه عمر کمک می‌کند.

سؤال 10: قابلیت‌ها در نسخه‌های SQL Server یکسان‌اند؟

خیر. گزینه‌های Online، Resumable، فشرده‌سازی، Columnstore مرتب و جزئیات سرویس‌های ابری با نسخه و Edition تفاوت دارند. Syntax نهایی و محدودیت‌ها را همیشه با مستندات رسمی همان نسخه‌ای که در مقصد اجرا می‌شود تطبیق دهید.

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

پرسش مصاحبه 1: چگونه میان Scan و Seek تصمیم می‌گیرید؟

هیچ‌کدام همیشه بهتر نیست. برای خواندن درصد بزرگی از جدول، Scan ترتیبی ممکن است ارزان‌تر باشد. انتخاب با Cardinality، پوشش ستون‌ها، هزینه Lookup و Plan واقعی سنجیده می‌شود.

پرسش مصاحبه 2: چرا ترتیب ستون‌های ایندکس مهم است؟

ترتیب، محدوده قابل Seek و امکان پشتیبانی از Sort را تعیین می‌کند. Equality، Range، Join و توزیع داده بررسی می‌شوند و نتیجه با Queryهای اصلی آزمایش می‌شود.

پرسش مصاحبه 3: INCLUDE چه تفاوتی با Key دارد؟

ستون Key در ساختار مرتب و قابل استفاده برای جست‌وجو است؛ INCLUDE در سطح برگ برای پوشش خروجی نگهداری می‌شود و محدودیت عرض کلید را افزایش نمی‌دهد، اما فضا و هزینه Update دارد.

پرسش مصاحبه 4: چه زمانی Columnstore پیشنهاد می‌دهید؟

برای Scan، Aggregate و گزارش تحلیلی روی حجم زیاد داده. اندازه جدول، الگوی بارگذاری، تعداد Update تک‌ردیفی، Rowgroup و نیاز Real-Time Analytics در انتخاب Clustered یا Nonclustered دخیل‌اند.

پرسش مصاحبه 5: چگونه ایندکس اضافی را تشخیص می‌دهید؟

تعریف هم‌پوشان، Usage Stats در یک دوره کامل کسب‌وکار، Query Store و هزینه Update بررسی می‌شوند. حذف ابتدا در محیط آزمایشی و سپس با Rollback روشن انجام می‌شود.

چک‌لیست نهایی طراحی و استقرار

• Query هدف و معیار موفقیت دقیق تعریف شده است.
• Plan، IO، CPU و زمان مبنا ذخیره شده‌اند.
• ایندکس‌های موجود و امکان ادغام بررسی شده‌اند.
• نوع Rowstore، Columnstore یا تخصصی آگاهانه انتخاب شده است.
• کلید، ترتیب، INCLUDE، Filter و گزینه‌ها اعتبارسنجی شده‌اند.
• هزینه DML، Log، فضا، Backup و نگهداری برآورد شده است.
• آزمون Staging با حجم و هم‌زمانی مشابه تولید انجام شده است.
• پنجره اجرا، پایش، معیار توقف و Rollback مشخص هستند.
• کنترل Performance پس از استقرار زمان‌بندی شده است.
• مالک، دلیل و تاریخ بازبینی ایندکس مستند شده‌اند.

جمع‌بندی

CREATE INDEX یک خانواده از تصمیم‌های طراحی است، نه یک فرمان یکسان برای همه مسائل. Rowstore برای دسترسی نقطه‌ای و محدوده‌ای، Columnstore برای تحلیل حجیم و ساختارهای XML، Spatial و Full-Text برای مدل داده تخصصی به کار می‌روند. Filter و INCLUDE نیز تعریف را دقیق‌تر و کم‌هزینه‌تر می‌کنند.

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

برای ادامه، آموزش تخصصی دستور موردنیاز را از فهرست زیر باز کنید و مثال‌ها را ابتدا در پایگاه آزمایشی اجرا کنید. نام جدول، ستون و گزینه‌های نسخه مقصد باید پیش از استفاده در Production تطبیق داده شوند.

• CREATE INDEX — ساخت ایندکس استاندارد
• CREATE CLUSTERED INDEX — ساخت ایندکس خوشه‌ای
• CREATE NONCLUSTERED INDEX — ساخت ایندکس غیرخوشه‌ای
• CREATE UNIQUE INDEX — ساخت ایندکس یکتا
• CREATE COLUMNSTORE INDEX — ساخت ایندکس ستونی
• CREATE CLUSTERED COLUMNSTORE INDEX — ساخت ایندکس ستونی خوشه‌ای
• CREATE NONCLUSTERED COLUMNSTORE INDEX — ساخت ایندکس ستونی غیرخوشه‌ای
• CREATE INDEX ... WHERE — ساخت ایندکس فیلترشده
• CREATE INDEX ... INCLUDE — ساخت ایندکس با ستون‌های INCLUDE
• CREATE XML INDEX — ساخت ایندکس XML
• CREATE SPATIAL INDEX — ساخت ایندکس مکانی
• CREATE FULLTEXT INDEX — ساخت ایندکس جست‌وجوی تمام‌متن
• CREATE INDEX ... WITH (DROP_EXISTING = ON) — بازسازی یا تغییر ایندکس با DROP_EXISTING