SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’

Many times I have seen that the index is disabled when there is large update operation on the table. Bulk insert of very large file updates in any table using SSIS is usually preceded by disabling the index and followed by enabling the index. I have seen many developers running the following query to disable the index.

USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO

While enabling the same index, I have seen developers using the following INCORRECT syntax, which results in error.

USE AdventureWorks
GO
----INCORRECT Syntax Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact ENABLE
GO

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘ENABLE’.

This is because once the index is disabled, it cannot be enabled, but it must be rebuilt. The following syntax will enable and rebuild the index with optimal performance.

USE AdventureWorks
GO

----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO

I hope that now you have understood why enabling this syntax in the index throws an error and also how to enable an index with optimal performance.

If you want to read just the syntax, I have written a similar article earlier SQL SERVER – Disable Index – Enable Index – ALTER Index.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Scripts
Previous Post
SQL SERVER – Advanced T-SQL with Itzik Ben-Gan – A Dream Coming True
Next Post
SQL SERVER – Tuning the Performance of Change Data Capture in SQL Server 2008

Related Posts

12 Comments. Leave new

  • Hey! this is good one….really helped me…

    Thanks

    Reply
  • gr8 post. very helpful indeed.

    could you please spare a minute to answer my question below.
    I hv sql 2008 installed. Whenever a new db is created, there is a path defaulted for the data and log files.
    Is there any global setting available where this path can be changed, which would be reflected whenever a new db is created.

    thx in advance.
    Denny

    Reply
  • Hi Pinal,

    Once again a nice post.

    @denny,

    You can change the default path for the data and log file in the server properties.

    Right click on the SQL instance –> Database Settings.

    In the Database setting page, under “Database Default Locations’, you can set your custom path for both data and log file.

    Hope this will help.

    Thanks.
    Sudeepta.

    Reply
  • Hi Pinal,

    Once again a nice post.

    @denny,

    One step missed in earlier comment, find the correct steps mentioned below.

    You can change the default path for the data and log file in the server properties.

    Right click on the SQL instance –> Properties –>Database Settings.

    In the Database setting page, under “Database Default Locations’, you can set your custom path for both data and log file.

    Hope this will help.

    Thanks.
    Sudeepta.

    Reply
  • Hi pinal,

    Can u pls tell me how to delete duplicate records.

    Thanks,
    Naresh

    Reply
  • Good one!

    Reply
  • Hi,

    I just wonder if you have any idea how to make it more flexible e.g. DISABLE / REBUILD all indexes in a table from stored procedure based on sys.tables and specified parameters like @table_name and @status -DISABLE / REBUILD

    Thanks ahead,

    Reply
  • Thanks a lot, Sepia. You save me life and carrier :-)

    Vlad.

    Reply
  • Hello again,

    Unfortunately, your idea to ‘ALTER INDEX ALL…’ will not work in my case because disable all including clustered for Primary Key.
    I have created more specific store procedure. This one is using only developer created indexes started with ‘IC_’. Also I figure out one issue with it: it has to be deployed in very db for it is working only for one database (you can not specify db name and use it as parameter). I could not fix it, however I left sys.tables inner join for future . In case if you have any idea to make more flexible, please, share this info.

    — !!!!!!!! one stored procedure for every database !!!!!!!!!———

    IF EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[Index__DISABLE_REBUILD]’)
    AND type in (N’P’, N’PC’))
    DROP PROCEDURE [dbo].[Index__DISABLE_REBUILD]
    GO
    create proc Index__DISABLE_REBUILD
    —- DISABLE/REBUILD indexes in a db for any table based on parameters
    — e.g.
    — EXEC Index__DISABLE_REBUILD ‘Finance_Detail_Fact’, ‘DISABLE’
    — EXEC Index__DISABLE_REBUILD ‘Finance_Detail_Fact’,’REBUILD’
    — EXEC Index__DISABLE_REBUILD ‘LOSSES’, ‘DISABLE’
    — EXEC Index__DISABLE_REBUILD ‘LOSSES’,’REBUILD’
    @table_name nvarchar(100), @status varchar(10)
    as
    begin
    Declare c Cursor For select distinct
    ‘ALTER INDEX [‘+ i.name +’] on ‘+ t.name +’ ‘+ @status
    from sys.tables t inner join
    sys.schemas s on t.schema_id = s.schema_id
    inner join sys.indexes i on i.object_id = t.object_id
    inner join sys.index_columns ic on ic.object_id = t.object_id
    inner join sys.columns c on c.object_id = t.object_id
    and ic.column_id = c.column_id
    where i.name like ‘IC_%’
    and t.name = @table_name
    Open c
    FETCH NEXT FROM c INTO @Table_name
    While @@Fetch_Status=0 Begin
    — print @Table_name
    exec ( @Table_name)
    Fetch next From c into @Table_name
    End
    Close c
    Deallocate c
    end

    Thanks,
    Vlad

    Reply
  • Enable All Index of Table:

    Example:

    ALTER INDEX ALL ON [dbo].[Products]
    REBUILD;
    GO

    Reply

Leave a Reply