1) ایندکس چیه؟ (تشبیه ساده)

مثل فهرست آخرِ کتاب: به‌جای اینکه کل صفحات رو بگردی، با فهرست می‌تونی سریعاً صفحهٔ موردنظر رو پیدا کنی.
در دیتابیس هم ایندکس ساختاری است که اجازه می‌دهد SQL Server ردیف‌ها را سریع‌تر پیدا کند.

2) فرق خوشه‌ای (Clustered) و غیرخوشه‌ای (Nonclustered)

• Clustered index: ترتیب فیزیکی ردیف‌های جدول را مشخص می‌کند. هر جدول حداکثر یک ایندکس خوشه‌ای دارد.
• Nonclustered index: یک ساختار جداگانه (B-Tree) است که مقادیر کلید ایندکس را نگه می‌دارد و در برگ‌ها (leaf level) اشاره‌گری به رکوردهای اصلی دارد. این اشاره‌گر می‌تواند:
  • اگر جدول دارای clustered index باشد: شامل مقدار کلید خوشه‌ای (clustering key) باشد.
  • اگر جدول heap (بدون clustered) باشد: شامل RID (Row Identifier) باشد.

3) ساختار داخلی (B-Tree) — خیلی خلاصه

• Root -> Intermediate pages -> Leaf pages.
• در leaf صفحات، کلیدهای ایندکس و اشاره‌گر به ردیف‌های داده ذخیره می‌شود.
• این ساختار باعث می‌شود جستجوها (seek) بسیار سریع باشند.

4) کی از Nonclustered استفاده می‌کنیم؟

• ستون‌هایی که در WHERE, JOIN, ORDER BY, GROUP BY زیاد استفاده می‌شوند.
• وقتی می‌خواهیم یک query سریع با کمترین خواندن صفحات داشته باشیم.
• زمانی که می‌خواهیم یک covering index بسازیم تا lookup به جدول اصلی حذف شود.

5) سینتکس‌های مهم — مثال‌ها

ایندکس ساده:

CREATE NONCLUSTERED INDEX IX_Customers_LastName
ON dbo.Customers(LastName);

ایندکس ترکیبی (Composite):

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders(CustomerId, OrderDate);

ایندکس با ستون‌های INCLUDE (برای پوشش دادن ستون‌های غیرکلیدی):

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId)
INCLUDE (TotalAmount, OrderStatus);

INCLUDE ستون‌ها را به برگ ایندکس اضافه می‌کند بدون اینکه آن‌ها به کلید ایندکس تبدیل شوند — برای کاهش Lookup مفید است.

ایندکس فیلترشده (برای زیرمجموعه‌ای از داده‌ها):

CREATE NONCLUSTERED INDEX IX_Orders_Active
ON dbo.Orders(CustomerId)
WHERE IsActive = 1;

ایندکس یکتا (می‌تواند nonclustered باشد):

CREATE UNIQUE NONCLUSTERED INDEX IX_Users_Email
ON dbo.Users(Email);

حذف ایندکس:

DROP INDEX IX_Customers_LastName ON dbo.Customers;

تنظیمات اضافی (مثلاً آنلاین، fillfactor):

CREATE NONCLUSTERED INDEX IX_Name ON dbo.T(Name)
WITH (ONLINE = ON, FILLFACTOR = 90);

توجه: ONLINE = ON در همه ادیشن‌ها پشتیبانی نمی‌شود یا بر حسب نسخه/ادیشن متفاوت است.

6) Covering index چیه؟ چرا مهمه؟

اگر ایندکس شامل همهٔ ستون‌هایی باشه که یک query نیاز داره، SQL Server نیازی به خواندن جدول اصلی (lookup) نداره — فقط از ایندکس پاسخ می‌ده (Index Seek → مستقیم). این باعث کاهش I/O و بهبود سرعت میشه.

مثال:

-- Query:
SELECT OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = 123;

-- Index covering:
CREATE NONCLUSTERED INDEX IX_Orders_CustId ON dbo.Orders(CustomerId)
INCLUDE (OrderDate, TotalAmount);

در این حالت SQL Server می‌تونه فقط از ایندکس جواب بده.

7) ترتیب ستون‌ها در ایندکس ترکیبی مهمه (Left-most prefix)

اگر ایندکس روی (A, B, C) ساخته شده:

• Queryهایی که روی A یا A,B شرط دارند از ایندکس استفاده می‌کنند.
• Query فقط با شرط روی B به‌صورت مؤثر از آن استفاده نمی‌کند.
  پس ترتیب ستون‌ها را براساس فراخوانی‌های متداول و Selectivity (تمایز مقادیر) انتخاب کن.

---

8) مزایا و معایب Nonclustered Index

مزایا:

• خواندن (SELECT) را سرعت می‌دهد.
• می‌توان چند ایندکس nonclustered روی یک جدول داشت.

معایب:

• هر ایندکس نوشتن (INSERT/UPDATE/DELETE) را کندتر می‌کند (چون ایندکس نیز باید به‌روزرسانی شود).
• فضای ذخیره‌سازی بیشتری مصرف می‌کند.
• نیاز به نگهداری (بازسازی، بررسی fragmentation، آمار) دارد.

---

9) نکات عملی و محدودیت‌ها

• اندازه کلید ایندکس: کی‌-کلیدها محدودیت اندازه‌ای دارند (حداکثر ~900 بایت در بسیاری موارد). ستون‌های بزرگ را در INCLUDE قرار بده.
• کلید خوشه‌ای به عنوان اشاره‌گر: در جداول دارای clustered index، مقدار کلید خوشه‌ای به nonclustered index اضافه می‌شود و فضای بیشتری مصرف می‌کند.
• از ایندکس روی ستون‌هایی با selectivity پایین (مثلاً boolean که فقط 0/1 داره) معمولاً نتیجهٔ مطلوب نمی‌گیریم مگر با filtered index.
• آمار (statistics) ایندکس برای بهینه‌سازی پلان مهم است — باید به‌روز باشد (auto-update usually ON but sometimes manual update needed).
• بررسی fragmentation با sys.dm_db_index_physical_stats و بازسازی با ALTER INDEX ... REBUILD یا REORGANIZE.

---

10) بررسی استفاده و نگهداری — دستورات مفید

چک کردن ایندکس‌های یک جدول:

SELECT name, type_desc, is_unique
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Orders');

بررسی استفاده (چه قدر ایندکس استفاده شده):

SELECT *
FROM sys.dm_db_index_usage_stats
WHERE object_id = OBJECT_ID('dbo.Orders');

بررسی fragmentation:

SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'LIMITED');

بازسازی:

ALTER INDEX IX_Orders_CustomerId_OrderDate ON dbo.Orders REBUILD WITH (FILLFACTOR = 90);
-- یا
ALTER INDEX ALL ON dbo.Orders REORGANIZE;

به‌روز کردن آمار:

UPDATE STATISTICS dbo.Orders IX_Orders_CustomerId_OrderDate;
-- یا کلی:
EXEC sp_updatestats;

---

11) مثال کامل کوچک — نشان دادن جریان

فرض جدول ساده:

CREATE TABLE dbo.Products (
  ProductID INT IDENTITY PRIMARY KEY,
  CategoryId INT,
  Name NVARCHAR(200),
  Price DECIMAL(10,2),
  IsActive BIT
);

-- اگر روی CategoryId جستجو زیاد داریم:
CREATE NONCLUSTERED INDEX IX_Products_Category ON dbo.Products(CategoryId)
INCLUDE (Price, Name);

Query:

SELECT Name, Price
FROM dbo.Products
WHERE CategoryId = 5 AND IsActive = 1;

اگر IsActive در INCLUDE یا شرط ایندکس فیلترشده باشه، احتمالاً query کاملاً از ایندکس استفاده می‌کند (بدون lookup).

---

12) نکات نهایی / چک‌لیست سریع

• ایندکس برای ستون‌هایی که در WHERE / JOIN / ORDER BY زیاد استفاده می‌شوند بساز.
• ترتیب ستون‌ها در ایندکس ترکیبی خیلی مهم است.
• از INCLUDE برای اضافه کردن ستون‌های مورد نیاز به جای کلید استفاده کن.
• مراقب هزینهٔ نوشتن باش (insert/update/delete).
• از ایندکس فیلترشده برای داده‌های تحت‌مجموعه استفاده کن.
• مرتب بررسی کن که ایندکس‌ها واقعاً استفاده می‌شوند (dmv ها) و ایندکس‌های بلااستفاده را حذف کن.
• بررسی fragmentation و بازسازی را در برنامه نگهداری قرار بده.