Solved

How to disable-enable index in SQL 2000 and 2005

Posted on 2011-09-26
32
329 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:rayluvs
  • 13
  • 10
  • 3
  • +3
32 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 67 total points
ID: 36598110
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
 

Author Comment

by:rayluvs
ID: 36598153
This world for SQL 2000 & 2005?
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 266 total points
ID: 36598158
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36598169
SQL server 2005
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 266 total points
ID: 36598172
Hi,

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

- Bhavesh
0
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 68 total points
ID: 36598241
0
 

Author Comment

by:rayluvs
ID: 36598686
How do we delete and re-create an index in SQL 2000?
0
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 68 total points
ID: 36598715
- 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
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 266 total points
ID: 36598720
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 66 total points
ID: 36598738
note you may also wish to drop and recreate any indexed views you have which reference your base tables...
0
 

Author Comment

by:rayluvs
ID: 36598770
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
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 266 total points
ID: 36598789
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
 

Author Comment

by:rayluvs
ID: 36599016
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
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 266 total points
ID: 36599088
hi,

while generating script, there is an option for scripting indexes.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36599091
sorry image is not attached.
F06ST13.JPG
0
 

Author Comment

by:rayluvs
ID: 36599232
Havent seened it but will try at the office
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 33 total points
ID: 36707728
ALTER INDEX [City] ON [dbo].[Customers] DISABLE
0
 

Author Comment

by:rayluvs
ID: 36710689
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
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 266 total points
ID: 36714751
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
 

Author Comment

by:rayluvs
ID: 36716300
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
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 67 total points
ID: 36716315
for SQL 2005 it is correct
0
 

Author Comment

by:rayluvs
ID: 36716369
For SQL 2000?
0
 

Author Comment

by:rayluvs
ID: 36716389
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
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 266 total points
ID: 36716687
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
 

Author Comment

by:rayluvs
ID: 36716843
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
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 266 total points
ID: 36716918
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 66 total points
ID: 36716978
<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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36717106
Thank u LowFatSpread sir.... :-)
0
 

Author Comment

by:rayluvs
ID: 36717110
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36717160
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
 

Author Comment

by:rayluvs
ID: 36717269
We try
0
 

Author Closing Comment

by:rayluvs
ID: 36718440
Thanx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now