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

October 27, 2009 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 6 Comments

6 Responses

on October 27, 2009 at 4:37 pm | Reply Prashant

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

Thanks
on October 27, 2009 at 8:57 pm | Reply denny

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
on October 28, 2009 at 1:02 pm | Reply deep

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.
on October 28, 2009 at 1:03 pm | Reply deep

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.
on November 19, 2009 at 12:36 pm | Reply Naresh

Hi pinal,

Can u pls tell me how to delete duplicate records.

Thanks,
Naresh
on November 19, 2009 at 7:38 pm | Reply Brian Tkatch

@Naresh

http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

Pinal has a good deal of articles here. I found that one by searching for: delete duplicate records