اسکریپت لیست تمامی Index های تعریف شده در یک دیتابیس

اسکریپت لیست تمامی Index های تعریف شده در یک دیتابیس

در دیتابیس‌های SQL Server، ایندکس‌ها یکی از مهم‌ترین ابزارها برای افزایش سرعت اجرای Queryها هستند.
زمانی که تعداد جدول‌ها و حجم اطلاعات یک دیتابیس افزایش پیدا می‌کند، بررسی وضعیت Indexها اهمیت بیشتری پیدا می‌کند؛ زیرا ایندکس نامناسب، تکراری یا بدون استفاده می‌تواند علاوه بر اشغال فضای دیسک، باعث افزایش زمان عملیات‌هایی مانند INSERT، UPDATE و DELETE شود.
یکی از کارهای کاربردی برای مدیر دیتابیس و برنامه‌نویس SQL Server، مشاهده فهرست تمام Indexهای تعریف‌شده در یک دیتابیس است.
با استفاده از یک Query مناسب می‌توان نام ایندکس، جدول مربوطه، ستون‌های Index، نوع Index و وضعیت Unique بودن آن را مشاهده کرد.

 

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

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

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

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

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

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

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

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

• Indexهای موجود در دیتابیس را شناسایی کنید.

• متوجه شوید هر Index متعلق به کدام جدول است.

• ستون‌های استفاده‌شده در هر Index را مشاهده کنید.

• Indexهای Unique و غیر Unique را از یکدیگر تشخیص دهید.

• نوع Index را بررسی کنید.

• Indexهای اضافی یا مشابه را برای بررسی بیشتر پیدا کنید.

• ساختار ایندکس‌های دیتابیس را مستندسازی کنید.
  
  
  

اسکریپت مشاهده تمامی Indexهای دیتابیس

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

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,
    i.is_unique_constraint AS IsUniqueConstraint,
    c.name AS ColumnName,
    ic.key_ordinal AS ColumnOrder
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON i.object_id = t.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
ORDER BY
    t.name,
    i.name,
    ic.key_ordinal;

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

بررسی خروجی Query

در خروجی این اسکریپت چند ستون مهم مشاهده خواهید کرد.

• TableName

  نام جدولی که Index روی آن ایجاد شده است را نمایش می‌دهد.
  برای مثال:

Customers
Orders
Products

• IndexName

  نام Index را نمایش می‌دهد.
  برای مثال:

IX_Customers_Name
IX_Orders_CustomerId
PK_Products

• IndexType

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

CLUSTERED
NONCLUSTERED

• IsUnique

  این ستون مشخص می‌کند که Index از نوع Unique است یا خیر.
  اگر مقدار آن 1 باشد، Index Unique است و اگر مقدار آن 0 باشد، Unique نیست.

• IsPrimaryKey

  این ستون نشان می‌دهد که Index مربوط به Primary Key است یا خیر.
  مقدار 1 یعنی Index مربوط به یک Primary Key است.

• IsUniqueConstraint

  این ستون مشخص می‌کند که Index به دلیل یک Unique Constraint ایجاد شده است یا خیر.

• ColumnName

  نام ستونی که در Index استفاده شده است را نمایش می‌دهد.
  برای مثال ممکن است یک Index روی ستون زیر ایجاد شده باشد:

CustomerId

FirstName
LastName

• ColumnOrder

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

CREATE INDEX IX_Customers_Name
ON Customers (FirstName, LastName);

نمایش ستون‌های Include شده در Index

در Indexهای Nonclustered ممکن است علاوه بر ستون‌های اصلی Index، ستون‌هایی با استفاده از INCLUDE اضافه شده باشند.
برای مشاهده این ستون‌ها می‌توان Query را کمی تغییر داد:

SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    c.name AS ColumnName,
    ic.key_ordinal AS KeyOrder,
    ic.is_included_column AS IsIncludedColumn
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON i.object_id = t.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
ORDER BY
    t.name,
    i.name,
    ic.key_ordinal;

ستون IsIncludedColumn مشخص می‌کند که ستون موردنظر، ستون اصلی Index است یا به صورت Included Column در Index قرار گرفته است.
اگر مقدار این ستون 1 باشد، ستون به صورت Included در Index قرار دارد.

نمایش اطلاعات Indexهای یک جدول خاص

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

SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique,
    c.name AS ColumnName,
    ic.key_ordinal AS ColumnOrder
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON i.object_id = t.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'
ORDER BY
    i.name,
    ic.key_ordinal;

در این مثال فقط Indexهای جدول Customers نمایش داده می‌شوند.

نکته مهم درباره Indexهای اضافی  در دیتابیس

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

جمع‌بندی

برای مدیریت صحیح SQL Server، شناخت Indexهای موجود در دیتابیس اهمیت زیادی دارد.
با استفاده از Viewهای سیستمی مانند sys.indexes، sys.index_columns، sys.tables و sys.columns می‌توان اطلاعات کاملی درباره Indexهای تعریف‌شده به دست آورد.
اسکریپت ارائه‌شده در این مقاله امکان مشاهده نام جدول، نام Index، نوع Index، Unique بودن، Primary Key بودن و ستون‌های استفاده‌شده در Index را فراهم می‌کند.
این Query می‌تواند در بررسی ساختار دیتابیس، مستندسازی، عیب‌یابی Performance و شناسایی Indexهای مشابه یا اضافی مورد استفاده قرار گیرد. البته تصمیم‌گیری درباره حذف یا تغییر Indexها باید با بررسی Queryها، Execution Plan و میزان استفاده واقعی از Index انجام شود.