Disabling an Index

Question

Post reply

Disabling an Index

Karthikeyan-418364

Mr or Mrs. 500

Points: 540
More actions
September 30, 2008 at 11:23 am

#193355

Hi all,
I need to disable an index from a table on certain condition from a stored procedure. How can i do this? Is it good to use DDL commands inside an stored procedure?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

My first question is why do you need to disable the index?

😎

Karthikeyan-418364 Mr or Mrs. 500 Points: 540 More actions · Answer 2

i'm updating one clustered index column. If i do with index it takes time to execute, if i do the same update without index its faster. so due to performance i want to disable index. is it good idea or any other way to do it?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Actually, that's one of the reasons they state in BOL for disabling an index. Go to books on line and check out ALTER INDEX.

😎

dfine Ten Centuries Points: 1251 More actions · Answer 4

Hi,

I don't think you can disable the idnex.

instead you can drop and re create it.

If your table contains huge no of records with clustered index created

then re creating index will also slow.

[font="Calibri"]Raj[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

dfine (9/30/2008)
Hi,
I don't think you can disable the idnex.
instead you can drop and re create it.
If your table contains huge no of records with clustered index created
then re creating index will also slow.

Actually, in SQL 2005 you can disable an index with ALTER INDEX. Check BOL example E. After disabling the index, to enable the index again, you have to use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING.

😎