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 (http://blog.SQLAuthority.com)

About these ads

13 thoughts on “SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’

  1. 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

    Like

  2. 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.

    Like

  3. 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.

    Like

  4. Pingback: SQL SERVER – Disable Clustered Index and Data Insert Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  6. 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,

    Like

  7. Hi Vlad,

    Go through Pinal’s article

    http://blog.sqlauthority.com/2010/05/12/sql-server-understanding-alter-index-all-rebuild-with-disabled-clustered-index/

    This would give you a basic knowledge of the commands. Now to put alter index command in a stored procedure, you can use code below:

    create proc abc.myproc1
    @tabname nvarchar(100), @status varchar(10)
    as
    begin
    declare @sqlbuff nvarchar(255)
    set @sqlbuff = ‘Alter index all on abc.’ + @tabname + ‘ ‘ + @status
    print @sqlbuff
    exec (@sqlbuff)
    end
    go

    exec abc.myproc1 @tabname= test , @status = disable

    This would display the command that will be executed by the stored procedure as well.

    Cheers, Sepia

    Like

  8. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s