اسکریپت لیست Index های جداول یک دیتابیس در SQL Server

اسکریپت لیست Index های جداول یک دیتابیس در SQL Server

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

چرا باید Indexهای دیتابیس را بررسی کنیم؟

با گذشت زمان ممکن است تعداد زیادی Index روی جداول ایجاد شود.
بعضی از این Indexها ممکن است دیگر استفاده نشوند یا Indexهای مشابه و تکراری ایجاد شده باشند.
وجود Index مناسب باعث بهبود عملکرد Queryها می‌شود، اما تعداد زیاد Index نیز همیشه مفید نیست.
هنگام اجرای عملیات‌هایی مانند INSERT، UPDATE و DELETE، SQL Server باید Indexهای مرتبط را نیز به‌روزرسانی کند.
بنابراین مدیر دیتابیس یا برنامه‌نویس باید بتواند فهرستی از Indexهای موجود را مشاهده و بررسی کند.

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

اسکریپت نمایش Indexهای جداول دیتابیس

یکی از روش‌های ساده برای دریافت اطلاعات Indexها استفاده از Viewهای سیستمی SQL Server مانند sys.indexes، sys.index_columns، sys.tables و sys.columns است.
اسکریپت زیر نام جدول، نام Index، نوع Index و ستون‌های مربوط به آن را نمایش می‌دهد:

SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique,
    i.is_primary_key AS IsPrimaryKey,
    STRING_AGG(c.name, ', ') AS Columns
FROM sys.tables AS t
INNER JOIN sys.indexes AS i
    ON t.object_id = i.object_id
INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
WHERE i.index_id > 0
GROUP BY
    t.name,
    i.name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key
ORDER BY
    t.name,
    i.name;

این Query اطلاعات Indexهای دیتابیس جاری را نمایش می‌دهد.

بررسی بخش‌های مختلف اسکریپت لیست Index های جداول یک دیتابیس

در قسمت اول اسکریپت، از sys.tables برای دریافت اطلاعات جدول‌ها استفاده شده است:

sys.tables

این View سیستمی اطلاعات جدول‌های موجود در دیتابیس را در اختیار ما قرار می‌دهد.
سپس با استفاده از:

sys.indexes

اطلاعات مربوط به Indexهای هر جدول دریافت می‌شود.
ارتباط جدول و Index از طریق object_id انجام می‌شود:

ON t.object_id = i.object_id

در مرحله بعد برای مشخص کردن ستون‌هایی که در هر Index قرار دارند، از sys.index_columns استفاده می‌کنیم.

sys.index_columns

این View مشخص می‌کند هر Index شامل چه ستون‌هایی است.
در نهایت با اتصال sys.columns می‌توان نام واقعی ستون‌ها را به دست آورد.

نمایش نوع Index  در SQL Server

یکی از اطلاعات مهمی که در خروجی مشاهده می‌کنیم، نوع Index است.
در اسکریپت از عبارت زیر استفاده شده است:

i.type_desc AS IndexType

مقدار type_desc نوع Index را به شکل قابل خواندن نمایش می‌دهد.
برای مثال ممکن است مقادیری مانند موارد زیر مشاهده کنید:

CLUSTERED
NONCLUSTERED
XML
SPATIAL
COLUMNSTORE

رایج‌ترین انواع Index در جداول معمولی SQL Server، Indexهای Clustered و Nonclustered هستند.

تشخیص Primary Key

در بسیاری از دیتابیس‌ها Primary Key نیز دارای Index است.
برای تشخیص اینکه یک Index مربوط به Primary Key است، از ویژگی زیر استفاده شده است:

i.is_primary_key

اگر مقدار این ستون 1 باشد، Index مربوط به Primary Key است.
برای مثال خروجی می‌تواند چیزی شبیه این باشد:

TableName    IndexName        IndexType       IsPrimaryKey
Customers    PK_Customers     CLUSTERED       1
Customers    IX_Customers     NONCLUSTERED    0
Orders       PK_Orders       CLUSTERED       1

به این ترتیب می‌توان به سرعت متوجه شد کدام Indexها مربوط به کلید اصلی هستند.

تشخیص Unique Index

یکی دیگر از اطلاعات مهم، Unique بودن Index است.
در اسکریپت از ویژگی زیر استفاده شده است:
i.is_unique AS IsUnique
اگر مقدار IsUnique برابر 1 باشد، Index از نوع Unique است.
Unique Index تضمین می‌کند مقادیر موجود در ستون یا ترکیب ستون‌های مربوط به Index، تکراری نباشند.
این نوع Index معمولاً برای پیاده‌سازی محدودیت‌های یکتا نیز کاربرد دارد.

حذف Indexهای بدون نام  در SQL Server

در بعضی شرایط ممکن است Index خاصی نام مشخصی نداشته باشد.
اگر هدف ما نمایش Indexهای دارای نام باشد، می‌توان شرط زیر را به Query اضافه کرد:

AND i.name IS NOT NULL

در نتیجه فقط Indexهایی که نام دارند نمایش داده می‌شوند.

نمایش Indexهای یک جدول خاص  در SQL Server

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

WHERE i.index_id > 0
  AND t.name = 'Customers'

نسخه کامل‌تر Query به شکل زیر خواهد بود:

SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique,
    i.is_primary_key AS IsPrimaryKey,
    STRING_AGG(c.name, ', ') AS Columns
FROM sys.tables AS t
INNER JOIN sys.indexes AS i
    ON t.object_id = i.object_id
INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
WHERE i.index_id > 0
  AND t.name = 'Customers'
GROUP BY
    t.name,
    i.name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key;

اهمیت بررسی Indexهای اضافی در SQL Server

یکی از دلایل مهم برای تهیه لیست Indexها، پیدا کردن Indexهای اضافی یا مشابه است.
فرض کنید یک جدول دارای چندین Index باشد که روی ستون‌های مشابه ساخته شده‌اند.
این موضوع می‌تواند باعث افزایش حجم دیتابیس و افزایش هزینه عملیات نوشتن شود.
به همین دلیل قبل از ایجاد Index جدید بهتر است Indexهای موجود را بررسی کنیم.
البته صرفاً مشاهده لیست Indexها برای حذف آن‌ها کافی نیست.
قبل از حذف یک Index باید میزان استفاده از آن، Queryهای وابسته، Execution Plan و شرایط کاری دیتابیس بررسی شود.

نکته مهم درباره ترتیب ستون‌ها

در Indexهای چندستونه، فقط دانستن نام ستون‌ها کافی نیست؛ ترتیب قرار گرفتن ستون‌ها نیز اهمیت دارد.
برای مثال فرض کنید Index زیر وجود داشته باشد:

IX_Orders_Customer_Date
(CustomerId, OrderDate)

این Index لزوماً برای Queryهایی که فقط بر اساس OrderDate جستجو می‌کنند، همان کارایی را ندارد که برای جستجو بر اساس CustomerId یا ترکیب CustomerId و OrderDate دارد.
بنابراین هنگام بررسی Indexها باید به ترتیب ستون‌های آن‌ها نیز توجه شود.