مقدمه

CREATE INDEX ... WITH (DROP_EXISTING = ON) یکی از دستورهای مهم طراحی و بهینه‌سازی در Microsoft SQL Server است. هدف آن جایگزینی تعریف ایندکس موجود در یک عملیات کنترل‌شده برای تغییر کلید، INCLUDE یا نوع ساختار است. تعریف درست می‌تواند خواندن منطقی، CPU و زمان پاسخ را کاهش دهد، اما تعریف نامناسب فضای ذخیره‌سازی و هزینه INSERT، UPDATE و DELETE را بالا می‌برد.

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

برای مشاهده نقشه کامل خانواده ایندکس‌ها، ابتدا راهنمای جامع دستورات ساخت ایندکس در SQL Server را مطالعه کنید. این صفحه جایگاه بازسازی یا تغییر ایندکس با DROP_EXISTING را در کنار Rowstore، Columnstore و ایندکس‌های تخصصی نشان می‌دهد.

تعریف و کاربرد CREATE INDEX ... WITH (DROP_EXISTING = ON)

بازسازی یا تغییر ایندکس با DROP_EXISTING در گروه نگهداری و مهاجرت ایندکس قرار می‌گیرد. تعریف ایندکس یک تصمیم فیزیکی درباره نحوه دسترسی به داده است؛ موتور پایگاه داده همچنان نتیجه منطقی Query را یکسان نگه می‌دارد، اما مسیر رسیدن به نتیجه می‌تواند از Scan پرهزینه به Seek یا پردازش ستونی هدفمند تغییر کند.

مهم‌ترین کاربرد این دستور، جایگزینی تعریف ایندکس موجود در یک عملیات کنترل‌شده برای تغییر کلید، INCLUDE یا نوع ساختار است. پیش از اجرا باید مالکیت Schema، مجوز ALTER روی جدول، فضای کافی، سلامت داده و سازگاری گزینه‌ها با نسخه سرور بررسی شود. نام‌گذاری یکنواخت نیز عیب‌یابی، Deployment و نگهداری آینده را ساده‌تر می‌کند.

اصل مهندسی: ایندکس را برای Query واقعی بسازید، نه صرفاً برای ستونی که مهم به نظر می‌رسد. برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی بخشی از معیار پذیرش این تغییر است.

نحو عمومی دستور

CREATE INDEX IX_CREATE_INDEX_DROP_EXISTING
    ON dbo.TableName(KeyColumn, DateColumn DESC)
    INCLUDE (OutputColumn)
    WITH (DROP_EXISTING = ON, ONLINE = ON);
    

اجزای مهم تعریف

پیش‌نیازها و طراحی قبل از اجرا

ابتدا Query Store، Extended Events یا ابزار مانیتورینگ را برای یافتن Queryهای پرهزینه بررسی کنید. تعداد اجرا و اثر تجمعی مهم‌تر از کندی یک اجرای نادر است. سپس Actual Execution Plan را همراه با پارامتر واقعی ذخیره کنید تا مشکل Scan، Lookup، Sort، Hash Spill یا تخمین اشتباه قابل مشاهده باشد.

توزیع داده و Selectivity ستون‌ها را با Statistics و Queryهای شمارشی بررسی کنید. ستونی با چند مقدار تکراری همیشه کلید خوبی نیست، مگر آنکه با فیلتر، ستون دوم یا ساختار تخصصی ترکیب شود. نوع داده کلید نیز بر اندازه صفحه، Fan-out و هزینه حافظه اثر مستقیم دارد.

در مرحله استقرار، مدت ساخت، رشد Transaction Log، فضای آزاد Data File و TempDB، احتمال Blocking و امکان Online Operation را برآورد کنید. برای محیط حساس، اسکریپت بازگشت و معیار توقف باید پیش از شروع تأیید شده باشد.

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

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

مثال 1: ساخت پایه روی ستون پرتکرار با CREATE INDEX ... WITH (DROP_EXISTING = ON)

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

IF OBJECT_ID(N'dbo.DemoDropExistingE1', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE1;
    
    CREATE TABLE dbo.DemoDropExistingE1
    (
        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.DemoDropExistingE1
        (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_DemoDropExisting_E1
        ON dbo.DemoDropExistingE1(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E1
        ON dbo.DemoDropExistingE1(CustomerID, OrderDate) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE1')
      AND name = N'IX_DemoDropExisting_E1';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE1
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

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

مثال 2: کلید مرکب برای جست‌وجوی چندشرطی با CREATE INDEX ... WITH (DROP_EXISTING = ON)

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

IF OBJECT_ID(N'dbo.DemoDropExistingE2', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE2;
    
    CREATE TABLE dbo.DemoDropExistingE2
    (
        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.DemoDropExistingE2
        (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_DemoDropExisting_E2
        ON dbo.DemoDropExistingE2(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E2
        ON dbo.DemoDropExistingE2(CustomerID, OrderDate DESC) INCLUDE (Amount) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE2')
      AND name = N'IX_DemoDropExisting_E2';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE2
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

نکته کاربردی: ستون سمت چپ کلید نقش تعیین‌کننده دارد و تغییر ترتیب ستون‌ها می‌تواند شکل Seek را عوض کند. برای بازسازی یا تغییر ایندکس با DROP_EXISTING همچنین باید برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را در بازبینی نهایی ثبت کرد.

مثال 3: پشتیبانی از مرتب‌سازی نزولی با CREATE INDEX ... WITH (DROP_EXISTING = ON)

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

IF OBJECT_ID(N'dbo.DemoDropExistingE3', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE3;
    
    CREATE TABLE dbo.DemoDropExistingE3
    (
        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.DemoDropExistingE3
        (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_DemoDropExisting_E3
        ON dbo.DemoDropExistingE3(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E3
        ON dbo.DemoDropExistingE3(OrderDate DESC, CustomerID) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE3')
      AND name = N'IX_DemoDropExisting_E3';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE3
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

نکته کاربردی: جهت کلید زمانی مهم‌تر می‌شود که Query ترکیبی از چند ستون با جهت‌های متفاوت داشته باشد. برای بازسازی یا تغییر ایندکس با DROP_EXISTING همچنین باید برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را در بازبینی نهایی ثبت کرد.

مثال 4: استفاده در شرط و اتصال جدول‌ها با CREATE INDEX ... WITH (DROP_EXISTING = ON)

نمونه‌ای نزدیک به گزارش عملی ساخته می‌شود تا نقش ایندکس در Predicate و Join یا بازیابی ردیف‌های هدف دیده شود. در این مثال، هدف عملی جایگزینی تعریف ایندکس موجود در یک عملیات کنترل‌شده برای تغییر کلید، INCLUDE یا نوع ساختار است.

IF OBJECT_ID(N'dbo.DemoDropExistingE4', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE4;
    
    CREATE TABLE dbo.DemoDropExistingE4
    (
        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.DemoDropExistingE4
        (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_DemoDropExisting_E4
        ON dbo.DemoDropExistingE4(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E4
        ON dbo.DemoDropExistingE4(Status, CustomerID) INCLUDE (Amount) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE4')
      AND name = N'IX_DemoDropExisting_E4';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE4
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

نکته کاربردی: وجود ایندکس تضمین استفاده از آن نیست؛ Optimizer بر پایه آمار، تعداد ردیف و هزینه تخمینی تصمیم می‌گیرد. برای بازسازی یا تغییر ایندکس با DROP_EXISTING همچنین باید برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را در بازبینی نهایی ثبت کرد.

مثال 5: تنظیم گزینه‌های ساخت و نگهداری با CREATE INDEX ... WITH (DROP_EXISTING = ON)

یکی از گزینه‌های متداول ساخت ایندکس مانند FILLFACTOR، SORT_IN_TEMPDB یا فشرده‌سازی در یک سناریوی کنترل‌شده نمایش داده می‌شود. در این مثال، هدف عملی جایگزینی تعریف ایندکس موجود در یک عملیات کنترل‌شده برای تغییر کلید، INCLUDE یا نوع ساختار است.

IF OBJECT_ID(N'dbo.DemoDropExistingE5', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE5;
    
    CREATE TABLE dbo.DemoDropExistingE5
    (
        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.DemoDropExistingE5
        (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_DemoDropExisting_E5
        ON dbo.DemoDropExistingE5(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E5
        ON dbo.DemoDropExistingE5(CustomerID) INCLUDE (OrderDate, Amount) WITH (DROP_EXISTING = ON, FILLFACTOR = 90);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE5')
      AND name = N'IX_DemoDropExisting_E5';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE5
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

نکته کاربردی: هر گزینه هزینه و پیش‌نیاز خود را دارد؛ مقدار مناسب باید با نسخه SQL Server، Edition و الگوی بار واقعی آزموده شود. برای بازسازی یا تغییر ایندکس با DROP_EXISTING همچنین باید برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را در بازبینی نهایی ثبت کرد.

مثال 6: رفتار با NULL و داده‌های اختیاری با CREATE INDEX ... WITH (DROP_EXISTING = ON)

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

IF OBJECT_ID(N'dbo.DemoDropExistingE6', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE6;
    
    CREATE TABLE dbo.DemoDropExistingE6
    (
        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.DemoDropExistingE6
        (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_DemoDropExisting_E6
        ON dbo.DemoDropExistingE6(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E6
        ON dbo.DemoDropExistingE6(Status, CustomerID) INCLUDE (ExternalCode) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE6')
      AND name = N'IX_DemoDropExisting_E6';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE6
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

نکته کاربردی: NULL مقدار عادی نیست و قواعد یکتایی، فیلتر و برآورد Cardinality را باید آگاهانه بررسی کرد. برای بازسازی یا تغییر ایندکس با DROP_EXISTING همچنین باید برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را در بازبینی نهایی ثبت کرد.

مثال 7: سناریوی مرزی با دامنه تاریخی با CREATE INDEX ... WITH (DROP_EXISTING = ON)

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

IF OBJECT_ID(N'dbo.DemoDropExistingE7', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE7;
    
    CREATE TABLE dbo.DemoDropExistingE7
    (
        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.DemoDropExistingE7
        (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_DemoDropExisting_E7
        ON dbo.DemoDropExistingE7(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E7
        ON dbo.DemoDropExistingE7(OrderDate, CustomerID) INCLUDE (Amount) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE7')
      AND name = N'IX_DemoDropExisting_E7';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE7
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

نکته کاربردی: در بازه‌های بسیار بزرگ، به‌روز بودن Statistics و هماهنگی نوع پارامتر با نوع ستون اهمیت ویژه دارد. برای بازسازی یا تغییر ایندکس با DROP_EXISTING همچنین باید برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را در بازبینی نهایی ثبت کرد.

مثال 8: گزارش سازمانی و ستون‌های خروجی با CREATE INDEX ... WITH (DROP_EXISTING = ON)

نمونه گزارش فروش یا عملیات با چند ستون خروجی ارائه می‌شود تا مرز میان کلید جست‌وجو و داده موردنیاز نمایش مشخص شود. در این مثال، هدف عملی جایگزینی تعریف ایندکس موجود در یک عملیات کنترل‌شده برای تغییر کلید، INCLUDE یا نوع ساختار است.

IF OBJECT_ID(N'dbo.DemoDropExistingE8', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE8;
    
    CREATE TABLE dbo.DemoDropExistingE8
    (
        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.DemoDropExistingE8
        (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_DemoDropExisting_E8
        ON dbo.DemoDropExistingE8(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E8
        ON dbo.DemoDropExistingE8(CustomerID, Status) INCLUDE (OrderDate, Amount, Description) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE8')
      AND name = N'IX_DemoDropExisting_E8';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE8
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

نکته کاربردی: هدف کاهش خواندن صفحه و حذف عملیات اضافه است، نه افزودن همه ستون‌های جدول به هر ایندکس. برای بازسازی یا تغییر ایندکس با DROP_EXISTING همچنین باید برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را در بازبینی نهایی ثبت کرد.

مثال 9: روش اشتباه و نسخه اصلاح‌شده با CREATE INDEX ... WITH (DROP_EXISTING = ON)

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

IF OBJECT_ID(N'dbo.DemoDropExistingE9', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE9;
    
    CREATE TABLE dbo.DemoDropExistingE9
    (
        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.DemoDropExistingE9
        (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'در انتظار بررسی');
    
    -- روش اشتباه: ساخت ایندکس بسیار عریض بدون بررسی الگوی Query
    -- نسخه اصلاح‌شده و هدفمند در ادامه اجرا می‌شود.
    CREATE INDEX IX_DemoDropExisting_E9
        ON dbo.DemoDropExistingE9(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E9
        ON dbo.DemoDropExistingE9(CustomerID, OrderDate) INCLUDE (Amount, Description) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE9')
      AND name = N'IX_DemoDropExisting_E9';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE9
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

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

مثال 10: کنترل نتیجه و سنجش کارایی با CREATE INDEX ... WITH (DROP_EXISTING = ON)

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

IF OBJECT_ID(N'dbo.DemoDropExistingE10', N'U') IS NOT NULL
        DROP TABLE dbo.DemoDropExistingE10;
    
    CREATE TABLE dbo.DemoDropExistingE10
    (
        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.DemoDropExistingE10
        (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_DemoDropExisting_E10
        ON dbo.DemoDropExistingE10(CustomerID);
    
    CREATE INDEX IX_DemoDropExisting_E10
        ON dbo.DemoDropExistingE10(CustomerID, OrderDate DESC) INCLUDE (Status, Amount) WITH (DROP_EXISTING = ON);
    
    SELECT name, type_desc, is_unique, has_filter
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.DemoDropExistingE10')
      AND name = N'IX_DemoDropExisting_E10';
    
    SELECT CustomerID, OrderDate, Amount
    FROM dbo.DemoDropExistingE10
    WHERE CustomerID = 101
    ORDER BY OrderDate DESC;
    

نکته کاربردی: نتیجه نهایی باید با Actual Execution Plan، STATISTICS IO و زمان اجرا در محیط آزمایشی مقایسه شود. برای بازسازی یا تغییر ایندکس با DROP_EXISTING همچنین باید برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را در بازبینی نهایی ثبت کرد.

خطاهای رایج و روش عیب‌یابی

خطای Duplicate Name زمانی رخ می‌دهد که نام ایندکس روی همان جدول از قبل وجود داشته باشد. پیش از Deployment با sys.indexes وضعیت را بررسی کنید و تصمیم بگیرید عملیات باید Create، Rebuild یا جایگزینی با DROP_EXISTING باشد. حذف خودکار ایندکس بدون شناخت وابستگی‌ها خطرناک است.

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

اگر دستور موفق بود اما Query سریع نشد، مسئله الزاماً از Create نیست. تبدیل ضمنی، عبارت غیرSARGable، Statistics قدیمی، پارامتر نامناسب یا Key Lookup پرهزینه می‌تواند باعث کنار گذاشته‌شدن ایندکس شود. Actual Plan پاسخ دقیق‌تری از حدس ارائه می‌دهد.

در این موضوع، برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی مهم‌ترین کنترل اختصاصی است. خطای عملیاتی فقط پیام SQL Server نیست؛ رشد Log، Blocking طولانی یا افزایش هزینه DML نیز یک شکست استقرار محسوب می‌شود.

ملاحظات کارایی و پایش

قبل و بعد از تغییر، SET STATISTICS IO و SET STATISTICS TIME را برای Query ثابت اجرا کنید. Logical Reads معیار مناسبی برای کاهش کار موتور است، اما باید CPU، Elapsed Time، Memory Grant و Waitها نیز کنار آن دیده شوند. یک نتیجه سریع در Cache گرم ممکن است تصویر ناقصی بدهد.

نمای sys.dm_db_index_usage_stats تعداد Seek، Scan، Lookup و Update را نشان می‌دهد، ولی پس از Restart ریست می‌شود. داده پایش را دوره‌ای ذخیره کنید تا ایندکس کم‌مصرف با ایندکس تازه‌ساخته اشتباه نشود. Query Store نیز تغییر Plan و Regression را قابل پیگیری می‌کند.

هر ایندکس جدید هزینه نوشتن دارد. اگر جدول تراکنشی پرتغییر است، تعداد و عرض کلیدها را محدود کنید. برای گزارش‌های حجیم، ساختار Columnstore یا گزارش جداگانه ممکن است از چند ایندکس Rowstore عریض مناسب‌تر باشد.

Fragmentation تنها معیار نگهداری نیست. روی ساختارهای کوچک اثر آن ناچیز است و در Columnstore باید کیفیت Rowgroup و تعداد ردیف‌های Deleted نیز دیده شود. برنامه نگهداری باید با نوع بازسازی یا تغییر ایندکس با DROP_EXISTING سازگار باشد.

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

1. نیاز را با Query واقعی و Plan معتبر اثبات کنید.
2. تعریف‌های موجود را برای هم‌پوشانی و امکان ادغام بررسی کنید.
3. کلید را تا حد ممکن باریک و هدفمند نگه دارید.
4. گزینه‌های نسخه و Edition مقصد را پیش از استقرار کنترل کنید.
5. ساخت را در محیط آزمایشی با حجم نزدیک به تولید زمان‌گیری کنید.
6. اثر روی DML، Log، Backup و Replication را بسنجید.
7. پس از استقرار Plan و شاخص‌های عملکرد را دوباره اندازه بگیرید.
8. مالک، دلیل ایجاد و تاریخ بازبینی ایندکس را مستند کنید.
9. برای بازسازی یا تغییر ایندکس با DROP_EXISTING، برآورد Log، فضای موقت، قفل، گزینه ONLINE و برنامه بازگشت در محیط عملیاتی را به چک‌لیست اضافه کنید.
10. در صورت نبود منفعت پایدار، با برنامه کنترل‌شده تغییر را بازگردانید.

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

سؤال 1: CREATE INDEX ... WITH (DROP_EXISTING = ON) دقیقاً چه کاری انجام می‌دهد؟

CREATE INDEX ... WITH (DROP_EXISTING = ON) ساختاری ایجاد می‌کند که برای جایگزینی تعریف ایندکس موجود در یک عملیات کنترل‌شده برای تغییر کلید، INCLUDE یا نوع ساختار طراحی شده است. موتور SQL Server با توجه به Predicate، آمار و هزینه تخمینی تصمیم می‌گیرد از آن استفاده کند؛ بنابراین ایجاد ساختار به‌تنهایی تضمین‌کننده سریع‌ترشدن همه Queryها نیست.

سؤال 2: چه زمانی بازسازی یا تغییر ایندکس با DROP_EXISTING انتخاب مناسبی است؟

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

سؤال 3: آیا افزودن این ایندکس هزینه پروژه را کاهش می‌دهد؟

اگر گلوگاه درست تشخیص داده شده باشد، کاهش زمان پاسخ، مصرف CPU و تعداد خواندن می‌تواند هزینه زیرساخت و پشتیبانی را کم کند. در پروژه حرفه‌ای باید هزینه ساخت، فضای دیسک، Log و نگهداری DML نیز در محاسبه بازگشت سرمایه وارد شود.

سؤال 4: برای سفارش طراحی بازسازی یا تغییر ایندکس با DROP_EXISTING چه اطلاعاتی لازم است؟

طرح جدول، Queryهای واقعی، Actual Execution Plan، حجم و رشد داده، نسخه SQL Server، پنجره نگهداری و شاخص‌های SLA اطلاعات پایه هستند. بدون این داده‌ها پیشنهاد ایندکس بیشتر شبیه حدس است تا یک تصمیم مهندسی قابل اندازه‌گیری.

سؤال 5: تفاوت این روش با یک ایندکس عمومی چیست؟

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

سؤال 6: آیا می‌توان پیاده‌سازی را بدون توقف سرویس انجام داد؟

این موضوع به نوع ایندکس، نسخه و Edition، اندازه جدول، گزینه ONLINE و الگوی تراکنش بستگی دارد. حتی عملیات Online نیز می‌تواند قفل‌های کوتاه یا مصرف منابع قابل توجه داشته باشد؛ بنابراین زمان‌بندی، پایش و برنامه بازگشت ضروری است.

سؤال 7: رایج‌ترین خطا هنگام اجرای CREATE INDEX ... WITH (DROP_EXISTING = ON) چیست؟

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

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

مقایسه باید با Query و پارامتر یکسان، Cache کنترل‌شده، Actual Execution Plan، SET STATISTICS IO و SET STATISTICS TIME انجام شود. فقط زمان یک اجرای منفرد معیار کافی نیست؛ خواندن منطقی، Memory Grant، Spill و اثر هم‌زمانی را هم ثبت کنید.

سؤال 9: بهترین روش نگهداری این ایندکس چیست؟

بر اساس نرخ تغییر، Fragmentation یا کیفیت Rowgroup، آمار مصرف و فضای ذخیره‌سازی برنامه نگهداری بسازید. بازسازی زمان‌بندی‌شده و کورکورانه مناسب نیست؛ تصمیم نگهداری باید مبتنی بر اندازه، Page Count، نوع ساختار و اثر واقعی بر Query باشد.

سؤال 10: این دستور با کدام نسخه‌های SQL Server سازگار است؟

هسته دستور در نسخه‌های پشتیبان آن قابل استفاده است، اما گزینه‌ها و قابلیت‌های تکمیلی میان نسخه‌ها، Editionها و سرویس‌های ابری تفاوت دارند. پیش از استفاده از ONLINE، فشرده‌سازی، Columnstore پیشرفته یا گزینه‌های خاص، مستندات همان نسخه سرور را کنترل کنید.

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

پرسش مصاحبه 1: چگونه ثابت می‌کنید بازسازی یا تغییر ایندکس با DROP_EXISTING لازم است؟

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

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

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

پرسش مصاحبه 3: اگر Optimizer از ایندکس استفاده نکرد چه می‌کنید؟

آمار، SARGability، تبدیل ضمنی، Parameter Sniffing، تخمین Cardinality و هزینه Lookup را بررسی می‌کنم. اجبار Hint آخرین انتخاب است، زیرا ممکن است با تغییر داده یا نسخه به تصمیم نامناسب تبدیل شود.

پرسش مصاحبه 4: چه شاخص‌هایی بعد از استقرار پایش می‌شوند؟

زمان پاسخ، Logical Read، CPU، تعداد Scan و Seek، نرخ Update، فضای ایندکس، انتظارهای قفل و تغییر Plan پایش می‌شوند. هدف اثبات پایداری منفعت در بار واقعی است.

پرسش مصاحبه 5: برنامه بازگشت برای این تغییر چیست؟

اسکریپت حذف یا بازگردانی تعریف قبلی، نسخه پشتیبان DDL، معیار توقف، زمان مجاز و مسئول تصمیم مشخص می‌شوند. برای جدول بزرگ، ظرفیت Log و فضای TempDB نیز قبل از عملیات برآورد می‌شود.

چک‌لیست نهایی

• نام پایگاه، Schema، جدول و ستون‌ها تأیید شده‌اند.
• نسخه پشتیبان تعریف قبلی و برنامه Rollback آماده است.
• فضای Data File، Log و TempDB بررسی شده است.
• Query و Plan مبنا پیش از تغییر ذخیره شده‌اند.
• مثال استقرار در محیط آزمایشی بدون خطا اجرا شده است.
• قفل، زمان اجرا و اثر روی کاربران پایش می‌شود.
• آمار IO و زمان پس از ساخت دوباره ثبت می‌شوند.
• مستندات و تاریخ بازبینی بعدی ثبت شده‌اند.

جمع‌بندی

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

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

برای مقایسه این روش با سایر ساختارها به مقاله مادر آموزش انواع دستور CREATE INDEX بازگردید.