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
rayluvsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pratima PharandeConnect With a Mentor Commented:
USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO

refer
http://blog.sqlauthority.com/2007/05/17/sql-server-disable-index-enable-index-alter-index/
0
 
rayluvsAuthor Commented:
This world for SQL 2000 & 2005?
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Pratima PharandeCommented:
SQL server 2005
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi,

In SQL 2000, You can not disable the index.
What you can do is, you can delete it and re-create.

- Bhavesh
0
 
eridanixConnect With a Mentor Commented:
0
 
rayluvsAuthor Commented:
How do we delete and re-create an index in SQL 2000?
0
 
eridanixConnect With a Mentor Commented:
- disable
IF EXISTS (select name FROM sysindexes WHERE name = name_of_index’)
     DROP INDEX name_of_table.name_of_index

- re-create
CREATE INDEX [name_if_index] ON [dbo].[name_of_tale] ([field(s)_to_index] asc)
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi,

check out this link.
http://msdn.microsoft.com/en-us/library/aa258260(v=sql.80).aspx

E.g.

To Drop Index

DROP INDEX tablename.indexname

Open in new window


To Create Index

CREATE INDEX indexName
   ON TableName (columnName)

Open in new window


-Bhavesh
0
 
LowfatspreadConnect With a Mentor Commented:
note you may also wish to drop and recreate any indexed views you have which reference your base tables...
0
 
rayluvsAuthor Commented:
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  
}

Open in new window

0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi,

You mean to say, some other applications going to create the index?

Are you able to access database server?

If yes Then generate script of indexes for backup purpose.

- Bhavesh
0
 
rayluvsAuthor Commented:
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?
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
hi,

while generating script, there is an option for scripting indexes.
0
 
Bhavesh ShahLead AnalysistCommented:
sorry image is not attached.
F06ST13.JPG
0
 
rayluvsAuthor Commented:
Havent seened it but will try at the office
0
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
ALTER INDEX [City] ON [dbo].[Customers] DISABLE
0
 
rayluvsAuthor Commented:
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?
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi,

PatelAlpesh - Author looking solution for SQL Server 2000, where you can not enable/disable index.

Author - You need to generate scripts, drop it and re-create the index.
0
 
rayluvsAuthor Commented:
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?
0
 
Pratima PharandeConnect With a Mentor Commented:
for SQL 2005 it is correct
0
 
rayluvsAuthor Commented:
For SQL 2000?
0
 
rayluvsAuthor Commented:
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
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi,

for sql server 2000, yes it is correct.

DROP INDEX tablename.indexname

CREATE INDEX indexName ON TableName (columnName)

For SQL Server 2005
Check out first post of pratima mam.

its not enable, its rebuild.
0
 
rayluvsAuthor Commented:
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?

0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Hi,

Ideally if index is in use then there should not be any kind of error.

But while creating index, create one by one so if is there any problem then you know exact point soon.

- Bhavesh
0
 
LowfatspreadConnect With a Mentor Commented:
<2c>

look ... stop saying programmer when talking about database objects, they are the responsibility of a database designer or DBA... and not within a programmers responsibility.

if you remove the existing indexes and replace them then yes you are exposed to potential problems...

but you should have a dba guiding you on this whole process, and he should be responsible for decidng what ,and how things need to be done to accomodate your new security requirements...

you need to have a performance validation , phase in the development lifecycle for this change where the effects of the change can be monitored and alleviated if necessary....

your systems should have a dba who is responsible for there security and performance, they should know/be able ro determine the scope of your proposed changes (it will all have to be referred back to business analysts as well of course to confirm that the business rules continue to function...)  

</2c>
0
 
Bhavesh ShahLead AnalysistCommented:
Thank u LowFatSpread sir.... :-)
0
 
rayluvsAuthor Commented:
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.
0
 
LowfatspreadCommented:
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
0
 
rayluvsAuthor Commented:
We try
0
 
rayluvsAuthor Commented:
Thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.