آموزش کامل DROP INDEX در SQL Server

مقدمه

DROP INDEX تعریف و ساختار فیزیکی یک ایندکس مستقل را حذف می‌کند. این تصمیم می‌تواند هزینه نوشتن و فضا را کاهش دهد، اما در صورت وابستگی Queryها باعث Scan، افزایش I/O و Regression شود؛ بنابراین Usage Stats به‌تنهایی برای حذف کافی نیست.

در این راهنما فرمان DROP INDEX از Syntax پایه تا کنترل خطا، پایش، سناریوی سازمانی و تصمیم کارایی بررسی می‌شود. همه مثال‌ها برای Microsoft SQL Server نوشته شده‌اند و پیش از استفاده در تولید باید با نام اشیا، نسخه و Edition محیط شما تطبیق داده شوند.

بازگشت به راهنمای جامع دستورات نگهداری ایندکس در SQL Server

تعریف، نحو و اجزای فرمان

Syntax استاندارد

DROP INDEX IF EXISTS index_name
    ON [schema_name].[table_name];

پارامترها و پیش‌نیازها

IF EXISTS اجرای تکرارپذیر را ممکن می‌کند.
index_name نام ساختار مستقل هدف است.
Schema و Table مالک ایندکس را مشخص می‌کنند.
ایندکس Constraint با ALTER TABLE DROP CONSTRAINT حذف می‌شود.

نوع خروجی

دستور Result Set ندارد. نبود نام در sys.indexes پس از اجرا، حذف موفق را نشان می‌دهد.

کاربرد واقعی

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

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

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

مثال 1: حذف ساده یک ایندکس

ایندکس آزمایشی ایجاد و سپس با Syntax جدید حذف می‌شود.

DROP TABLE IF EXISTS #IndexLab;
    CREATE TABLE #IndexLab
    (
        RowID int IDENTITY(1,1) NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        Amount decimal(12,2) NOT NULL
    );

    INSERT INTO #IndexLab (OrderDate, CustomerID, Amount)
    VALUES ('2026-01-01', 101, 125000),
           ('2026-01-02', 102, 98000),
           ('2026-01-03', 101, 143500);

    CREATE INDEX IX_IndexLab_OrderDate
    ON #IndexLab (OrderDate);

    DROP INDEX IX_IndexLab_OrderDate ON #IndexLab;
    SELECT COUNT(*) AS RemainingIndex FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IndexLab') AND name = N'IX_IndexLab_OrderDate';

شاخص	خروجی نمونه	توضیح
RemainingIndex	0	نتیجه نمایشی

DROP INDEX تعریف و ساختار را حذف می‌کند و با DISABLE متفاوت است.

مثال 2: حذف امن با IF EXISTS

اسکریپت تکرارپذیر است و اگر ایندکس وجود نداشته باشد خطا نمی‌دهد.

DROP TABLE IF EXISTS #IndexLab;
    CREATE TABLE #IndexLab
    (
        RowID int IDENTITY(1,1) NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        Amount decimal(12,2) NOT NULL
    );

    INSERT INTO #IndexLab (OrderDate, CustomerID, Amount)
    VALUES ('2026-01-01', 101, 125000),
           ('2026-01-02', 102, 98000),
           ('2026-01-03', 101, 143500);

    CREATE INDEX IX_IndexLab_OrderDate
    ON #IndexLab (OrderDate);

    DROP INDEX IF EXISTS IX_IndexLab_OrderDate ON #IndexLab;
    DROP INDEX IF EXISTS IX_IndexLab_OrderDate ON #IndexLab;
    SELECT N'Completed' AS ResultText;

شاخص	خروجی نمونه	توضیح
ResultText	Completed	نتیجه نمایشی

IF EXISTS از SQL Server 2016 در دسترس است و برای Deploymentهای تکرارپذیر مناسب است.

مثال 3: حذف چند ایندکس در یک Batch

دو ایندکس آزمایشی با یک دستور DROP INDEX حذف می‌شوند.

DROP TABLE IF EXISTS #IndexLab;
    CREATE TABLE #IndexLab
    (
        RowID int IDENTITY(1,1) NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        Amount decimal(12,2) NOT NULL
    );

    INSERT INTO #IndexLab (OrderDate, CustomerID, Amount)
    VALUES ('2026-01-01', 101, 125000),
           ('2026-01-02', 102, 98000),
           ('2026-01-03', 101, 143500);

    CREATE INDEX IX_IndexLab_OrderDate
    ON #IndexLab (OrderDate);
    CREATE INDEX IX_IndexLab_CustomerID ON #IndexLab(CustomerID);

    DROP INDEX IX_IndexLab_OrderDate ON #IndexLab,
               IX_IndexLab_CustomerID ON #IndexLab;

شاخص	خروجی نمونه	توضیح
DeletedIndexes	2	نتیجه نمایشی

قبل از حذف گروهی، وابستگی و Planهای مهم هر ایندکس را جداگانه بررسی کنید.

مثال 4: روش درست حذف ایندکس Constraint

ایندکس پشتیبان Primary Key با ALTER TABLE و حذف Constraint مدیریت می‌شود.

SELECT
        i.name,
        i.is_primary_key,
        i.is_unique_constraint
    FROM sys.indexes AS i
    WHERE i.object_id = OBJECT_ID(N'dbo.Customer')
      AND i.name = N'PK_Customer';

    -- پس از تأیید وابستگی Foreign Keyها:
    ALTER TABLE dbo.Customer
    DROP CONSTRAINT PK_Customer;

شاخص	خروجی نمونه	توضیح
is_primary_key	1	نتیجه نمایشی
روش حذف	ALTER TABLE DROP CONSTRAINT	نتیجه نمایشی

DROP INDEX برای ایندکس ساخته‌شده توسط PRIMARY KEY یا UNIQUE Constraint مجاز نیست.

مثال 5: بررسی میزان استفاده پیش از حذف

خواندن‌ها و نوشتن‌های ثبت‌شده از DMV گزارش می‌شوند.

SELECT
        i.name,
        Reads = COALESCE(s.user_seeks, 0)
              + COALESCE(s.user_scans, 0)
              + COALESCE(s.user_lookups, 0),
        Writes = COALESCE(s.user_updates, 0)
    FROM sys.indexes AS i
    LEFT JOIN sys.dm_db_index_usage_stats AS s
      ON s.database_id = DB_ID()
     AND s.object_id = i.object_id
     AND s.index_id = i.index_id
    WHERE i.object_id = OBJECT_ID(N'dbo.Sales')
      AND i.name = N'IX_Sales_LegacyCode';

شاخص	خروجی نمونه	توضیح
Reads	0	نتیجه نمایشی
Writes	154800	نتیجه نمایشی

صفر بودن Reads پس از Restart یا Failover کافی نیست؛ یک چرخه کاری کامل و Query Store را هم بررسی کنید.

مثال 6: رفتار امن با نام NULL

نام خالی پیش از ساخت فرمان حذف رد می‌شود.

DECLARE @IndexName sysname = NULL;

    IF NULLIF(LTRIM(RTRIM(@IndexName)), N'') IS NULL
        THROW 51040, N'نام ایندکس برای DROP INDEX الزامی است.', 1;

شاخص	خروجی نمونه	توضیح
Result	Controlled error 51040	نتیجه نمایشی

NULL نباید باعث حذف نامزد پیش‌فرض یا ساخت فرمان ناامن شود.

مثال 7: حذف پویا با اعتبارسنجی Catalog

فقط ایندکس غیروابسته و موجود، با نام Quote شده حذف می‌شود.

DECLARE @Schema sysname = N'dbo',
            @Table sysname = N'Sales',
            @Index sysname = N'IX_Sales_LegacyCode';

    IF EXISTS
    (
        SELECT 1 FROM sys.indexes
        WHERE object_id = OBJECT_ID(QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table))
          AND name = @Index
          AND is_primary_key = 0
          AND is_unique_constraint = 0
    )
    BEGIN
        DECLARE @Sql nvarchar(max) =
            N'DROP INDEX ' + QUOTENAME(@Index) + N' ON '
            + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N';';
        EXEC sys.sp_executesql @Sql;
    END;

شاخص	خروجی نمونه	توضیح
Index	IX_Sales_LegacyCode	نتیجه نمایشی
Action	DROP	نتیجه نمایشی

Catalog Check و QUOTENAME هر دو برای فرمان‌های مدیریتی پویا ضروری هستند.

مثال 8: ثبت تعریف پیش از حذف

تعریف کلیدها و ستون‌های Include برای امکان بازسازی بعدی Snapshot می‌شود.

SELECT
        i.name AS IndexName,
        c.name AS ColumnName,
        ic.key_ordinal,
        ic.is_included_column
    FROM sys.indexes AS i
    JOIN sys.index_columns AS ic
      ON ic.object_id = i.object_id
     AND ic.index_id = i.index_id
    JOIN sys.columns AS c
      ON c.object_id = ic.object_id
     AND c.column_id = ic.column_id
    WHERE i.object_id = OBJECT_ID(N'dbo.Sales')
      AND i.name = N'IX_Sales_LegacyCode'
    ORDER BY ic.key_ordinal, ic.index_column_id;

شاخص	خروجی نمونه	توضیح
Column	KeyOrdinal	Included
LegacyCode	1	0
OrderDate	0	1

پیش از DROP، Script ایجاد یا تعریف ساختاری را در کنترل نسخه نگه دارید.

مثال 9: جایگزینی با DROP_EXISTING

اگر هدف تغییر تعریف است، CREATE INDEX با DROP_EXISTING جایگزین حذف و ایجاد جداگانه می‌شود.

CREATE INDEX IX_Sales_OrderDate
    ON dbo.Sales (OrderDate, CustomerID)
    INCLUDE (Amount)
    WITH
    (
        DROP_EXISTING = ON,
        SORT_IN_TEMPDB = ON,
        MAXDOP = 2
    );

شاخص	خروجی نمونه	توضیح
Index	IX_Sales_OrderDate	نتیجه نمایشی
Result	Definition replaced	نتیجه نمایشی

DROP_EXISTING می‌تواند مسیر تغییر تعریف را بهینه‌تر و کنترل‌شده‌تر کند.

مثال 10: ارزیابی اثر حذف با Query Store

Queryهای وابسته به جدول و میانگین مدت آن‌ها پیش از تغییر ثبت می‌شوند.

SELECT TOP (20)
        q.query_id,
        p.plan_id,
        rs.avg_duration,
        rs.avg_logical_io_reads
    FROM sys.query_store_query AS q
    JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
    JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
    WHERE qt.query_sql_text LIKE N'%dbo.Sales%'
    ORDER BY rs.avg_logical_io_reads DESC;

شاخص	خروجی نمونه	توضیح
query_id	avg_duration	avg_reads
842	12500	18420

Baseline Query Store امکان تشخیص Regression و بازگرداندن سریع ایندکس را فراهم می‌کند.

ملاحظات Performance

حذف ایندکس بدون استفاده هزینه DML و فضا را کاهش می‌دهد، ولی داده DMV پس از Restart ریست می‌شود. Query Store، چرخه کامل کسب‌وکار، تعریف ایندکس و برنامه بازگشت باید بررسی شوند.

برای اندازه‌گیری معتبر، زمان Queryهای منتخب، Logical Read، Waitها و رشد Transaction Log را در یک بازه کاری مشابه مقایسه کنید. Cache گرم یا سرد، اجرای هم‌زمان Jobها و تغییر حجم داده می‌تواند نتیجه را منحرف کند؛ بنابراین یک نمونه منفرد مبنای تصمیم بلندمدت نیست.

خطاهای رایج

حذف بر پایه Reads صفر پس از Restart.
تلاش برای DROP ایندکس Primary Key.
نگه نداشتن Script بازسازی.
حذف هم‌زمان چند ایندکس مشابه.
Dynamic SQL بدون QUOTENAME.

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

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

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

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

سؤال متداول 1: DROP INDEX دقیقاً چه کاری انجام می‌دهد؟

این فرمان بخشی از چرخه مدیریت فیزیکی یا چرخه عمر ایندکس است. اثر دقیق آن به نوع فرمان، نوع ایندکس و State فعلی بستگی دارد؛ بنابراین پیش از اجرا Catalog Viewها و مستند نسخه نصب‌شده را بررسی کنید.

سؤال متداول 2: آیا DROP INDEX برای افراد مبتدی مناسب است؟

یادگیری Syntax ساده است، اما اجرای تولیدی نیازمند شناخت قفل، Log، tempdb، Availability و برنامه بازگشت است. ابتدا روی پایگاه آزمایشی و نسخه پشتیبان تمرین کنید.

سؤال متداول 3: هزینه اجرای DROP INDEX چگونه برآورد می‌شود؟

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

سؤال متداول 4: آیا اجرای DROP INDEX می‌تواند سرعت سامانه را بیشتر کند؟

ممکن است، اما تضمینی نیست. بهبود تنها وقتی رخ می‌دهد که مشکل واقعی با ساختار ایندکس مرتبط باشد؛ Query Store و معیارهای قبل و بعد باید اثر را ثابت کنند.

سؤال متداول 5: تفاوت DROP INDEX با فرمان‌های نزدیک چیست؟

REBUILD ساختار را دوباره می‌سازد، REORGANIZE مرتب‌سازی تدریجی است، DISABLE استفاده را متوقف می‌کند، DROP حذف دائمی است و PAUSE، RESUME و ABORT چرخه عملیات Resumable را مدیریت می‌کنند.

سؤال متداول 6: برای اجرای سازمانی DROP INDEX چه خدماتی لازم است؟

طراحی Job، پایش، گزارش خطا، آزمون بازیابی و تنظیم آستانه‌ها بخش‌های اصلی هستند. تیم آموزش یا مشاوره پایگاه داده می‌تواند اسکریپت را با SLA و معماری همان سازمان هماهنگ کند.

سؤال متداول 7: خطای رایج در DROP INDEX چیست؟

اجرای فرمان با نام یا State نامعتبر، کمبود فضا، محدودیت Edition، قفل Schema و فراموش کردن وابستگی‌ها از خطاهای رایج‌اند. ERROR_NUMBER و ERROR_MESSAGE را ثبت و خطا را دوباره THROW کنید.

سؤال متداول 8: DROP INDEX چه اثری بر Performance دارد؟

اثر می‌تواند هم مثبت و هم منفی باشد. CPU، I/O، Waitها، رشد Log و زمان Queryهای مهم را در بازه‌ای قابل مقایسه ثبت کنید و به یک درصد Fragmentation اکتفا نکنید.

سؤال متداول 9: Best Practice اجرای DROP INDEX چیست؟

دستور را هدفمند، تکرارپذیر، قابل ثبت و دارای Guard Clause بنویسید. نام اشیا در Dynamic SQL باید از Catalog اعتبارسنجی و با QUOTENAME محافظت شود.

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

Syntax پایه بسیاری از فرمان‌ها قدیمی است، ولی گزینه‌هایی مانند IF EXISTS، ONLINE، WAIT_AT_LOW_PRIORITY و RESUMABLE در نسخه‌ها و Editionهای متفاوت عرضه شده‌اند. سازگاری دقیق را با نسخه سرور و مستندات همان نسخه کنترل کنید.

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

سؤال مصاحبه 1: چه زمانی DROP INDEX را در Production اجرا می‌کنید؟

پاسخ خوب باید به اندازه ایندکس، بار کاری، SLA، ظرفیت Log، قفل‌ها و معیار قبل و بعد اشاره کند، نه فقط یک آستانه ثابت.

سؤال مصاحبه 2: چگونه ریسک Dynamic SQL را کاهش می‌دهید؟

نام‌ها از sys.indexes و sys.objects اعتبارسنجی می‌شوند، QUOTENAME اعمال می‌شود و مقادیر داده‌ای با sp_executesql پارامتری می‌شوند.

سؤال مصاحبه 3: چگونه موفقیت عملیات را ثابت می‌کنید؟

با ثبت زمان، State، Fragmentation، page_count، رشد Log و معیارهای Query Store در قبل و بعد، نتیجه قابل دفاع می‌شود.

سؤال مصاحبه 4: تفاوت نگهداری ایندکس و Update Statistics چیست؟

ساختار فیزیکی و توزیع آماری دو مسئله مرتبط ولی جدا هستند. Rebuild معمولاً آمار ایندکس را تازه می‌کند، Reorganize نیاز به تصمیم جدا برای Statistics دارد.

سؤال مصاحبه 5: در Availability Group چه چیزی مهم است؟

حجم Log تولیدشده، نرخ ارسال و Redo در Replicaها، فضای دیسک و مدت پنجره باید هم‌زمان پایش شوند.

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

نسخه، Edition و پشتیبانی Syntax کنترل شده است.
نام Schema، جدول و ایندکس از Catalog تأیید شده است.
فضای داده، tempdb و Transaction Log بررسی شده است.
معیارهای پیش از اجرا ثبت شده‌اند.
TRY/CATCH، لاگ و مسیر بازگشت آماده است.
نتیجه و اثر کارایی پس از اجرا مقایسه می‌شود.

خدمات برنامه‌نویسی و پایگاه داده

برنامه‌نویسی اصفهان؛ قبول سفارشات برنامه‌نویسی و پایگاه داده: 09131253620. انجام پروژه‌های برنامه‌نویسی، آموزش برنامه‌نویسی و آموزش پایگاه داده SQL Server با رویکرد عملی و سازمانی ارائه می‌شود.

جمع‌بندی

DROP INDEX زمانی ارزشمند است که مسئله، دامنه و هزینه آن اندازه‌گیری شده باشد. نمونه‌های این مقاله الگوی ساخت فرمان امن، کنترل State، ثبت نتیجه و ارزیابی کارایی را نشان دادند. پیش از اجرای Production، اسکریپت را روی نسخه‌ای مشابه آزمایش و معیار موفقیت را از قبل تعریف کنید.

مشاهده فهرست کامل دستورات نگهداری ایندکس در SQL Server

آموزش کامل DROP INDEX در SQL Server با مثال عملی و نکات Performance