DISABLE/ENABLE INDEXES QUESTION

[katgirl]

Hi,

I used the ALTER TABLE command to disable some indexes on a table. Now, of course, I can't find the command to ENABLE the indexes!

My other question is - will disabling the indexes make data loads faster? That is what I need. Seems easier than dropping/recreating the indexes.

Can someone please help me.

Thanks

 

[SQLBill]

What did you really do? You can Enable/Disable Triggers, but you can't Enable/Disable INDEXES. INDEXES are DROPPED or CREATED.

If you DROPPED the indexes, you are going to have to CREATE each one over again.

-SQLBill

Posting advice: FAQ481-4875

 

[katgirl]

Like I said, I disabled some indexes using the command:

ALTER INDEX index1 ON table1 DISABLE
GO

There is no corresponding ENABLE stmt. The closest I have come to an answer is that you can use REBUILD. But it doesn't work.

 

[katgirl]

from 2005 bol:

ALTER INDEX { index_name | ALL }
ON <object>
{ REBUILD
[ [ WITH ( <rebuild_index_option> [ ,...n ] ) ]
| [ PARTITION = partition_number
[ WITH ( <single_partition_rebuild_index_option>
[ ,...n ] )
]
]
]
| DISABLE
| REORGANIZE
[ PARTITION = partition_number ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
| SET ( <set_index_option> [ ,...n ] )
}
[ ; ]

 

[mrdenny]

You will need to rebuild the index to re-enable it.

... This clause is equivalent to DBCC DBREINDEX. [red]REBUILD enables a disabled index.[/red]...

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

mrdenny, i tried using the rebuild command, but it said it couldn't rebuild because it was disabled! but i used the "alter table" version of it, though

do you know if disabling and index makes data loads faster? i would think it would, just as dropping the index would, but i'm not 100% sure

thanks

 

[SQLBill]

Like I said, I disabled some indexes using the command:

ALTER INDEX index1 ON table1 DISABLE
GO

That's NOT what you said......you said you used ALTER TABLE and that's different.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

The ALTER INDEX ... REBUILD command should do the trick.

Yes disabling or dropping indexes will make data loading faster. However you will want to do some testing to ensure that the amount of time saved is less than the amount of time needed to recreate the indexes.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

yes, sqlbill - i stand corrected