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

Never update systems tables directly - a study in Agent job scheduling Expand / Collapse
Author
Message
Posted Tuesday, December 25, 2012 8:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, January 17, 2015 7:36 AM
Points: 424, Visits: 56
Good article.
Post #1400075
Posted Tuesday, March 24, 2015 10:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 5, 2015 3:08 PM
Points: 155, Visits: 623
Nakul Vachhrajani (9/16/2012)
It may be appealing to most developers to directly update the system tables because of the adrenalin rush associated with working directly at the root level, but it is not a recommended approach.

I've often wondered what additional tasks system sps were doing beyond updating tables and I appreciated the explanation, but I found the above comment to be somewhat silly and condescending.
I think it's more likely that developers and dbas are striving to avoid the rbar approach that is required when using stored procedures to update system tables one object at a time (that's how I came across this article) versus looking for some sort of a high.
But who knows, maybe some readers would get an "adrenalin rush" if they could do one efficient set-based insert instead of looping though hundreds of system stored procedure calls (and presumably hundreds of behind-the-scenes cache updates - each superceding the last). But for me it would be more of a lack of that nagging feeling of inefficiency I have every time I find myself writing yet another loop just to call sp_addthis or sp_deletethat repeatedly because SQL Server still doesn't officially support a set based update mechanism.
Hopefully sp_MSforeachdb and sp_MSforeachtable will soon become "documented" and are a sign that Microsoft recognizes how much busy work it is for us to write all these loops (yes, I know sp_MSforeach is still a loop deeper down, but at least I'm not typing "WHILE etc.").
Rant over, thanks for reading.



Post #1671106
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse