SQL Server : Enable and Disable Index

I spent half of my day today to enable indexes that i disabled yesterday during data load. I disabled most of the indexes in tables that I was loading to. Indexes slow down data loading as they have some overheads. After loading each record, SQL server goes on to update index definitions. This will impact the loading time.

I therefore usually disable the indexes before load.

Today I tried to re enable indexes using SQL Server Management Studio 2008R2.  To enable indexes I right clicked on the index name in the explorer and clicked 'Rebuild' . This took ages to run and bring up the next window which needs to be clicked to rebuild the index. I then went into the query editor window and enabled index manually using the below SQL query

ALTER INDEX index_name ON table_name REBUILD

It was a lot faster to rebuild it this way.

So remember: if the table is very small, go for index rebuilding using SQL Server Management Studio. Otherwise use the Query Editor and rebuild index manually.

To Disable Index, Management Studio is quite fast. just right click on the  index and select 'Disable'. this works like a treat.

or alternately , to manually disable index

ALTER INDEX index_name ON table_name DISABLE