How to disable-enable index in SQL 2000 and 2005
We are changing master ID codes for security purposes. And have come upon error messages regarding indexes. We would like disable the indexes, then do the SQL data update, finally rebuild or recreate the indexes.
What's the best way to go about it?
We running SQL 2000 and 2005
What's the best way to go about it?
We running SQL 2000 and 2005
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL server 2005
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do we delete and re-create an index in SQL 2000?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We checked the format for CREATE INDEX and saw a lot of options (see code). Since the indexes we are about delete and recreate are not from our tables, it's from another application developed long time ago, can there be an error when recreating? That is, that we may not use an option that the original programmer used?
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]
< index_option > :: =
{ PAD_INDEX |
FILLFACTOR = fillfactor |
IGNORE_DUP_KEY |
DROP_EXISTING |
STATISTICS_NORECOMPUTE |
SORT_IN_TEMPDB
}SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I meant that the DB has been here for a long time and the original programmer is not with us. But you are correct your recommendation in 'generate script'. We know how to generate the script for a table (tight-click and choose create), but how do we do this for Indexes?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry image is not attached.
F06ST13.JPG
F06ST13.JPG
ASKER
Havent seened it but will try at the office
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This will disable the Index, we assume that placing ENABLE at the end will enable it. When enabling will it recreate the index or update its contents?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To make sure, for disabling/enabling indexes in SQL 2005, we use
ALTER INDEX [City] ON [dbo].[Customers] DISABLE / ENABLE
For disabling/enabling indexes in SQL 2000, we use
DROP INDEX tablename.indexname
CREATE INDEX indexName ON TableName (columnName)
Is this correct?
ALTER INDEX [City] ON [dbo].[Customers] DISABLE / ENABLE
For disabling/enabling indexes in SQL 2000, we use
DROP INDEX tablename.indexname
CREATE INDEX indexName ON TableName (columnName)
Is this correct?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For SQL 2000?
ASKER
Just tested SQL 2005, the ENABLE doesn't work it gives
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ENABLE'.
Please advice
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ENABLE'.
Please advice
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found it, thanx!
Since the indexes we are about delete and recreate are from another application developed long time ago, can there be an error when recreating? That is, that we may not use an option that the original programmer used?
Since the indexes we are about delete and recreate are from another application developed long time ago, can there be an error when recreating? That is, that we may not use an option that the original programmer used?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thank u LowFatSpread sir.... :-)
ASKER
Good advice, we'll create them ine by one.
2c:
unfortunately, we can't afford a DBA; we're very small. Our apps DB are always created by the programmer. Nevertheless, we always review all development with a tedious validation prior going to production.
2c:
unfortunately, we can't afford a DBA; we're very small. Our apps DB are always created by the programmer. Nevertheless, we always review all development with a tedious validation prior going to production.
fine you employ an it specialist then who will wear many hats ... make sure he is wearing his shinyest DBA one when he undertakes this task.
good luck
good luck
ASKER
We try
ASKER
Thanx
ASKER