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
Posted Tuesday, December 25, 2012 8:41 AM


Group: General Forum Members
Last Login: Tuesday, June 16, 2015 2:34 AM
Points: 424, Visits: 60
Good article.
Post #1400075
Posted Tuesday, March 24, 2015 10:40 AM


Group: General Forum Members
Last Login: Tuesday, September 15, 2015 7:52 AM
Points: 162, Visits: 701
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