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:51 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
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" :D

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
Post #649233
Posted Tuesday, February 3, 2009 12:57 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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!
Post #649243
Posted Tuesday, February 3, 2009 2:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 7,928, Visits: 9,653
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
Post #649377
Posted Tuesday, February 3, 2009 3:00 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #649380
Posted Tuesday, February 3, 2009 3:03 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #649384
Posted Wednesday, February 4, 2009 2:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
Tom.Thomson (2/3/2009)
It seems expensive to me, re-ednabling the clustered index implies rebuilding every index on the table.


With SQL Server 2005 and greater, rebuilding the clustered index does not force a rebuild of all NC indexes.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #650408
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse