February 3, 2009 at 12:54 am
Comments posted to this topic are about the item SQL 2008 Indexes
February 3, 2009 at 12:56 am
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.
[font="Verdana"]Markus Bohse[/font]
February 3, 2009 at 5:53 am
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.
February 3, 2009 at 9:24 am
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 ;).
February 3, 2009 at 10:06 am
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.
February 3, 2009 at 10:10 am
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.
February 3, 2009 at 10:41 am
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
February 3, 2009 at 11:18 am
Yes, that makes sense since there is no way to take a table offline like you can in Oracle.
February 3, 2009 at 11:22 am
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.
February 3, 2009 at 12:23 pm
I think I'd go with the instead of triggers... if it saves me a full clustered index rebuild.
February 3, 2009 at 12:51 pm
Ninja's_RGR'us (2/3/2009)
I think I'd go with the instead of triggers... if it saves me a full clustered index rebuild.
True but like with every thing there is an "it depends" 😀
You will be kicking people out AFTER the "inserted" and "deleted" tables are created on tempdb. It "could" become a PIG!
In SQL 2000 inserted and deleted tables were built directly from the transaction log ... not any more 😉
* Noel
February 3, 2009 at 12:58 pm
Ah!, learn something new everyday :P.
That's a good it depends. There would need to have a buttload of traffic on that table to cause tempdb to crash or become a hotspot (unless it's already hot and getting worse because of that).
P.S. Just remember to deactivate the trigger before doing your big table load... that could really make a hot-spot of the server!
February 3, 2009 at 2:51 pm
noeld (2/3/2009)
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 😉
It seems expensive to me, re-ednabling the clustered index implies rebuilding every index on the table. If the DB design is reasonable, there are a maybe a small number of roles which have permissions on this table - altering those permissions seems pretty cheap and easy (and also provides the option of making the table read only, which is a little less drastic than making it completely inaccessible - particularly if your objective is, as you suggest, just to prevent changes).
Tom
February 3, 2009 at 3:00 pm
Tom.Thomson (2/3/2009)
noeld (2/3/2009)
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 😉
It seems expensive to me, re-ednabling the clustered index implies rebuilding every index on the table. If the DB design is reasonable, there are a maybe a small number of roles which have permissions on this table - altering those permissions seems pretty cheap and easy (and also provides the option of making the table read only, which is a little less drastic than making it completely inaccessible - particularly if your objective is, as you suggest, just to prevent changes).
That sounds nice when you control the code.
* Noel
February 3, 2009 at 3:03 pm
Oh And I have seen *MANY* people using simply db_datareader and db_datawriter.
You could take those permissions away and then the "entire" DB is inaccessible 🙁
* Noel
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply