SQL 2008 Indexes

Question

SQL 2008 Indexes

SA-1

SSCrazy Eights

Points: 8113
More actions
February 3, 2009 at 12:54 am

#218134

Comments posted to this topic are about the item SQL 2008 Indexes

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

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]

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 2

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 SSC Guru Points: 294069 More actions · Answer 3

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 ;).

Cliff Jones SSChampion Points: 10577 More actions · Answer 4

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 SSChampion Points: 10577 More actions · Answer 5

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 SSC Guru Points: 96590 More actions · Answer 6

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

Cliff Jones SSChampion Points: 10577 More actions · Answer 7

Yes, that makes sense since there is no way to take a table offline like you can in Oracle.

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 8

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 SSC Guru Points: 294069 More actions · Answer 9

I think I'd go with the instead of triggers... if it saves me a full clustered index rebuild.

noeld SSC Guru Points: 96590 More actions · Answer 10

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

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!

TomThomson SSC Guru Points: 104773 More actions · Answer 12

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

noeld SSC Guru Points: 96590 More actions · Answer 13

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

noeld SSC Guru Points: 96590 More actions · Answer 14

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

SQL 2008 Indexes

Cookies on SQLServerCentral