SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008 Indexes


SQL 2008 Indexes

Author
Message
SA-1
SA-1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4509 Visits: 944
Comments posted to this topic are about the item SQL 2008 Indexes
MarkusB
MarkusB
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18921 Visits: 4208
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
Tao Klerks
Tao Klerks
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2575 Visits: 1249
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)

Group: General Forum Members
Points: 131798 Visits: 9672
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 Wink.
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6379 Visits: 3648
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.
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6379 Visits: 3648
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.
noeld
noeld
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43386 Visits: 2052
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 Wink


* Noel
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6379 Visits: 3648
Yes, that makes sense since there is no way to take a table offline like you can in Oracle.
Tao Klerks
Tao Klerks
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2575 Visits: 1249
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)

Group: General Forum Members
Points: 131798 Visits: 9672
I think I'd go with the instead of triggers... if it saves me a full clustered index rebuild.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search