Why Are You Dropping and Recreating Your Indexes?

Do you have data load processes that drop indexes to optimize the load process?  Are you dropping those indexes in one step, then loading data into your table, only to recreate the indexes in a post data load step?  It is common practice to drop indexes and then create them to optimize the data load process. By not having the indexes at load time SQL Server can load the data much faster, because there are no indexes to update.  This is a common practice for optimizing loading large data warehouse table loads.

Does this sound familiar?  I have to ask you, why are you dropping and recreating your indexes, when you could just disable and then rebuild them.   By disabling an index, you have basically turned off the index, but have allowed SQL Server to retain the index definition in the database metadata.  If you want the index back all you need to do is rebuild the index.  

If you drop the index, SQL Server no longer retains the metadata for the index.  Therefore, if you want to recreate the index you will need to recreate the index with the CREATE INDEX statement.  Hopefully you can remember the exact syntax of your CREATE INDEX statement.  By disabling an index, you don’t have to remember the columns that make up your index, because SQL Server keeps that information for you.   Below is an example where I disabled an index, and then turned around and rebuilt it.  See how simple that it is. 

ALTER INDEX IX_Mytable_OrderDate ON MySchema.MyTable DISABLE;
GO
ALTER INDEX IX_Mytable_OrderDate ON MySchema.MyTable REBUILD;
GO
 
 

One last note. If you disable a clustered index, SQL Server makes the data for the table inaccessible until a rebuild operation.

See all articles by Greg Larsen