Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to Disable an index in sql server 2000 Expand / Collapse
Author
Message
Posted Tuesday, April 15, 2008 1:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211
Hi all,

I am using SQL SERVER 200.I want to disable created indexes on a table
i tried the following sql , it throwing error

alter index FIRST_REF on tblComponentComponentJoin DISABLE

can anybody help in this is highly appreciated


Thanks

shamsudheen
Post #484799
Posted Tuesday, April 15, 2008 1:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
There's no way to disable an index in SQL 2000. You'll have to drop it.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #484806
Posted Tuesday, April 15, 2008 1:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211
Thanks Gail Shaw

i have another question related to this topic. i am having a view of joining two table with one key and each table have multiple indexes.my question is when i run the view is it will use the indexes or not



thanks
Post #484808
Posted Tuesday, April 15, 2008 6:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
Maybe. It depends on the queries run, the data distribution, the indexes you have. It's not a question that can be answered in general.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #484911
Posted Tuesday, April 15, 2008 6:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 14,835, Visits: 27,311
First, having an index on a table won't slow down reads of that table assuming the reads use a different index. Second, get an execution plan for the query and then you'll know which indexes it uses or doesn't. Just a reminder, while an index scan is "using" the index, it isn't necessarily using it well.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #484933
Posted Thursday, April 17, 2008 11:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
With Gail and Grant of course. It also depends on how you and where you are putting the [where] condition in your join. Like what Gail said, there is no general answer for this. You will have to run the query and see the execution plan and see it from there.

Imagination is more important than knowledge-Albert Einstein
Post #486660
Posted Thursday, April 17, 2008 9:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211
SQL King (4/17/2008)
With Gail and Grant of course. It also depends on how you and where you are putting the [where] condition in your join. Like what Gail said, there is no general answer for this. You will have to run the query and see the execution plan and see it from there.


hi

in my query i only join with keys no filter condition . i found it is still using index.Thanks Gai,Grant and Sql king for your effort

Post #486882
Posted Thursday, April 17, 2008 10:19 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,198, Visits: 1,368
Check this whether it works or not:

ALTER INDEX [IX_name] ON table DISABLE
GO


I don't have 2000 so I can't test it but found this solution in 2-3 sites.

Hope it works.:)

Also read these articles:
http://blog.sqlauthority.com/2007/05/17/sql-server-disable-index-enable-index-alter-index/
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspX



Post #486890
Posted Friday, April 18, 2008 12:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
Anirban Paul (4/17/2008)
Check this whether it works or not:

ALTER INDEX [IX_name] ON table DISABLE
GO



Alter Index is only 2005 and higher. 2000 didn't have the command at all.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #486911
Posted Friday, April 18, 2008 12:44 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,198, Visits: 1,368
GilaMonster (4/18/2008)
Anirban Paul (4/17/2008)
Check this whether it works or not:

ALTER INDEX [IX_name] ON table DISABLE
GO



Alter Index is only 2005 and higher. 2000 didn't have the command at all.


As I told you I couldn't check as I found in under SQL Server 2000 in some sites. I never used it when I was using SQL Server 2000. I used to drop indexes. So when I saw in sites I thought let me put it in the forum to clear my doubts. Thanks Gail for your comment.

:):)



Post #486928
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse