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

SQL 2008 Indexes Expand / Collapse
Author
Message
Posted Tuesday, February 3, 2009 12:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 2:58 PM
Points: 1,141, Visits: 944
Comments posted to this topic are about the item SQL 2008 Indexes
Post #648612
Posted Tuesday, February 3, 2009 12:56 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:05 AM
Points: 4,427, Visits: 4,178
Ok, I got it right and I don't really want to complain about the question. In fact I think it's a pretty good question.
But what I find strange is that the question is placed under the header SQL 2008 indexes, but the link in the answer refers to BOL for SQL 2005.


Markus Bohse
Post #648614
Posted Tuesday, February 3, 2009 5:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
I agree, this is a very good question, but there's something I can't figure out or find in a quick search: Why would you ever want to disable a primary key or other clustered index??

Does anyone know of use cases?

Thanks,
Tao


http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #648748
Posted Tuesday, February 3, 2009 9:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
I don't know, maybe do a manual import and then delete the dupes??? That's not the way I'd do it, but who am I to say something on that subject ;).
Post #648985
Posted Tuesday, February 3, 2009 10:06 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
According to the guidelines, inserts will fail also. I agree, I am not sure why you would ever want to disable the clustered index. I can't come up with a sound reason.
Post #649047
Posted Tuesday, February 3, 2009 10:10 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
I guess it is a way to take the table offline while preparing to do something with it. That's all I can think of.
Post #649058
Posted Tuesday, February 3, 2009 10:41 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Actually I have had the need for this feature for a long time.
Suppose you need to prevent changes on ONE table while you are doing some maintenance work in a Db.
To Set a DB in read-only mode is too drastic, To Setup that table in a separated File and make it read only is inconvenient.
To setup INSTEAD OF/AFTER TRIGGERS is expensive in Server resources.

This solution seems cheap and Fast ;)




* Noel
Post #649104
Posted Tuesday, February 3, 2009 11:18 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
Yes, that makes sense since there is no way to take a table offline like you can in Oracle.
Post #649141
Posted Tuesday, February 3, 2009 11:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
Thanks Noel, that makes sense - I'm not sure it's very "cheap" because the clustered index gets rebuilt when it gets re-enabled (according to the BOL article), but still seems better than the alternatives!

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #649147
Posted Tuesday, February 3, 2009 12:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
I think I'd go with the instead of triggers... if it saves me a full clustered index rebuild.
Post #649202
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse