Indexes are probably the number one tool we have to improve performance. That said, there are times when we want to put that index on hold. While indexes dramatically improve read performance they do cause a slight dip in write performance. This isn’t significant most of the time but when doing a large load it can frequently be faster to get rid of the existing indexes and then put them back when you are done. I should probably point out here that I’ve never found a hard and fast rule about when this will improve performance and when it won’t. To a certain extent, it’s just experience and testing. Regardless, rather than dropping the index and then re-creating it (which can be a bit of a pain) the easiest thing to do is to just disable the index. The primary benefit here is that the code for the index stays in place (As does the disk space used by the index which could be a good thing or not depending on your point of view.) making restoring the index very very simple.

-- Set up demo table
CREATE TABLE DisableIndexes (
Id INT NOT NULL IDENTITY (1,1)
CONSTRAINT pk_ci_DisableIndexes PRIMARY KEY
,Col1 INT
,Col2 INT
);
CREATE NONCLUSTERED INDEX ix_DisableIndexes_Col1 ON DisableIndexes(Col1);
CREATE NONCLUSTERED INDEX ix_DisableIndexes_Col2 ON DisableIndexes(Col2);
GO

Disabling an individual index is pretty simple.

ALTER INDEX ix_DisableIndexes_Col1 ON DisableIndexes DISABLE;
GO
SELECT name, is_disabled 
FROM sys.indexes 
WHERE object_id = object_id('DisableIndexes');
GO

Easy right? So how about re-enabling it? Well, there is no ENABLE option on the ALTER INDEX command. For a good reason too. Once you disable the index SQL is no longer updating it. That means that the information there is completely invalid (or more importantly SQL has no idea what parts of it are or are not valid). So the only way to get it working again is to rebuild it.

ALTER INDEX ix_DisableIndexes_Col1 ON DisableIndexes REBUILD;
GO
SELECT name, is_disabled 
FROM sys.indexes 
WHERE object_id = object_id('DisableIndexes');
GO

Awesome! That was really easy, right? Of course on a large table it may take a while, but again, depending on the table size, index size, number of indexes etc, it can still be faster to disable the indexes, load the table, and rebuild the indexes than to just do the load with the indexes in place.

Now, I’m sure at least one of you is thinking “I can rebuild all of the indexes on a table in one command, can I disable them all with one command?” And yes dear reader you can .. but ..

ALTER INDEX ALL ON DisableIndexes DISABLE; 
GO
SELECT name, is_disabled 
FROM sys.indexes 
WHERE object_id = object_id('DisableIndexes');
GO

You’ll notice something important here, right? That the PRIMARY KEY (which also happens to be the clustered index) is also disabled. The biggest problem here is that the CI (clustered index) is disabled. Since the CI is the table this means that it’s now impossible to use the table.

-- Either command (or UPDATE/DELETE commands) will give the same error
SELECT * FROM DisableIndexes;
INSERT INTO DisableIndexes VALUES (1,1);

Msg 8655, Level 16, State 1, Line 36

The query processor is unable to produce a plan because the index ‘pk_ci_DisableIndexes’ on table or view ‘DisableIndexes’ is disabled.

Disability the primary key in and of itself isn’t a huge issue (remember that the PK doesn’t have to be the CI). It is a unique constraint though. And fair warning, if you disable a unique index then you will be able to insert non-unique data and the rebuild will fail if you do.

So, in the end, if you have a heap you can disable all of the non-clustered indexes at once. If you have a clustered index then you can’t. You could disable all of them and then rebuild just the CI but for a large table that could take quite a bit of extra, unnecessary time.