Solved

How to disable-enable index in SQL 2000 and 2005

Posted on 2011-09-26
32
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

697 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