Just my 2 cents...
I've always done updates the way you've posted. As some have said, it's saved my keester more than once.
I also hammer (in a mentor/nice way) the method onto the Devs and they get it. For those that worry about having both Rollback and Commit being commented out, I'll take the chance there for several reasons...
- We don't actually let ANYONE (including me, the DBA) do any kind of data updates in production "directly". We enforce the rule that there MUST be a script to do the job and that it MUST be fully auditable, which means there must be a ticket that covers the action, it must have gone through the Dev, QA, UAT gauntlet (and that can be done very quickly for urgencies/emergencies), and the script MUST ALWAYS have a hard-coded expected row count (which can sometimes be a close-enough value for rapidly changing tables).
- The hard-coded expected rowcount is required for several reasons. If first show due auditable due diligence on the part of the Developer and the whole process. It also forces people to pay attention during deployment to production because another auditable process we rigidly follow is that NO ONE IS ALLOWED TO DEPLOY THEIR OWN CODE TO PRODUCTION, PERIOD! Except for the addition of indexes, that includes me... the DBA.
- The biggest reason for all of this, especially the hard-coded expected row counts, it because MS and SQL Server have screwed us more than once. The worst example I saw was back around 2004 when a really simple UPDATE had a WHERE clause that was supposed to limit the update to where a column had a NULL value. It worked correctly in Dev and Staging. In prod, SQL Server ignored the WHERE clause (it turned out that there was actually a CONNECT item on the problem). Instead of the code updating a little over 1,000 customer's data, it updated the data for all 1.2 million customers. The DBA never caught it because there was no indication of what the rowcount should be during the run and prior to the commit and the DBA simply doesn't have the time to review every piece of code during deployment (we also fixed that after). So the DBA committed the code because no errors occurred.
The error was discovered the next morning... after the reporting server (which has copies of all the production tables) was updated. They went to do a restore because the damage was propagated by the nightly runs... yeah... that's when they also found out that backups had been silently failing for about 3 weeks.
It took 40 people more than 10 days to get us back into business by reloading 3 weeks of files in the correct order, doing the processing on them, making a shedload of manual entries from source documents, etc, etc, and it was never 100% correct for about 6 months.
A lot of things changed at that company because of that episode including them suddenly following the full development and deployment life cycle that I'd been trying to pound into their heads for a couple of years. Most importantly, hard-code rowcounts for updates along with manual Rollback/Commits were always required because of the failure we discovered (and was documented in a CONNECT item) clearly demonstrated that we couldn't rely on SQL Server for such things.
And, I'll gladly "take the hit" when a Developer sometimes forgets to do a Rollback/Commit on the Dev box. That causes a whole lot less damage (none) than a UPDATE going awry, even in Dev. I'll have some fun with a Developer that does sometimes forget but it's always done in a friendly manner and I NEVER "come down hard on" a Developer that has done such a thing.
Shifting gears a bit, we're starting to join the "Big boy's club". We have several databases teetering on the 1TB mark and two teetering on the 2 TB mark and several tables in the 100 to 300 GB range. I've made it so that some of these larger tables live in the own databases and are referenced from the main database by synonyms. Some of those tables have actually been split to multiple "sub-tables" using one form of partitioning or another and some of the tables have actually been split to separate databases using partitioned views. Of course, the whole reason for that is to be able to very quickly do "get back in business" partial restores of one form or another. Of course, I've also been able to make some of the legacy partitions (coming up to 120 monthly partitions on one table that we have to "keep the data forever" on) READ ONLY, which also has the benefit of greatly decreasing the time and space it takes to do nightly full backups.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)