There are few requirements in real world when Index on table needs to be disabled and re-enabled afterwards. e.g. DTS, BCP, BULK INSERT etc. Index can be dropped and recreated. I prefer to disable the Index if I am going to re-enable it again.
USE AdventureWorks GO----Diable Index ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE GO----Enable IndexALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD GO
Reference : Pinal Dave(http://www.SQLAuthority.com), BOL
pinaldave
If i disable an index and import a load of data, will the indexes:
- re-index when i re-enable the index
- re-index as the data is importing even though they’re disabled
- or do they not reindex until a dbreindex / alter table command is submitted
??
thanks
“ALTER INDEX”
won’t work with SQL 2000
Pls, help me!
I tried to convert all CHAR columns of a DB to VARCHAR in MS SQL-2000.
But because of the dependece of indexes my procedure fails.
If I drop the idx-es I don’t know how to create them back with all the previous atributes.
How can I just disable the idx-es and afterwords enable them?
If Iknow well, ALTER INDEX…DISABLE exists only in MS SQL-2005.
Thanks!
Hi Dave,
How to disable the index in SQL 2000?I tried alter index,but it didn’t work.Please help me on this matter.
I would like to know that what is the difference between index and nonclustered index?
Thanks
Hi ,
I want to disable a Primary Key Clustered Index made on five columns :
CREATE CLUSTERED INDEX PK_ArrangementMeasure ArrangementMeasure(ArrangementOID, ArrangementMeasureTypeOID, UnitOfMeasureOID,MeasurementEventOID,SourceSystemImplementationOID)
before a target table update/insert of 24 lakh/2 million records. Beacuse your command is useful for disabling index on 1 column how to do it for more thn one column…Early reply will be appreciated. Thank u.Please mail soln at my yahoo id ankur_argal@yahoo.com
@ Ankur,
I may be wrong but this is what I think.
The script just talks about index and on which table, no where it discuss columns.
You are talking about composite primary keys. One primary key will have only one clustered indexes no matter if it has one column or more than one column.
I dont think the script given by Pinal dave will change for a single column primary key or composite Primary key ( more than one column).
It makes sense to me, because no where in the script we mentioned column name, only two names are mentioned.,
1. one is index name
2. other is table name ( owner.table_name).
Hope this helps.
Imran.
This is helpful. However , I believe that rebuilding an index willl reorganize the data again (with new inserts). Suppose if I have 10 crores of records. Will it take too long to enable the index. I have tested it and it takes about 3 hours for 10 crores of records.
Also, if I do that it creates a huge log file , which takes a lot of space on server.
Is there a way to deal with this?
Is that
-Manish
I am using Microsoft SQL server 2008.
I am having trouble with this enable/disable option.
I disabled clustered index that I created and then try to insert some data and got the error message.
The query processor is unable to produce a plane because “index_name” on “table_name” is disabled.
Does anybody has alternative for SQL server 2008?
Thanks for help in advance.
-Manish
“Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt.”
From what I understand this means that when you disable the clustered index, the table becomes read-only.
It isn’t very clear though: it says the disabled clustered index must be dropped or rebuilt in order for the data to become update able again.
so, you need to drop the index to proceed.
Disabling a non-clustered index or clustered index on a view physically deletes the index data
I have table which has 16 lakhs records
i have to retrive the record below 4 seconds
now it is taking 20 seconds
i have created an index also for the id which has the primary key still this problem is there.
what should i have to do
please help me