Defensive Db Programming Chapter 04

Comments: No Comments
Published on: August 11, 2010

This week we will be checking out the fourth chapter of this book by Alex.  This is a continuous effort that started with my blog post on the first chapter.  In this chapter, Alex delves into a couple of features that are new with SQL 2005 that could affect your queries and alter the expected results.

The two features being described are:

  1. Snapshot Isolation
  2. Merge

In SQL 2005 we were introduced to the Snapshot Isolation features.  The default isolation prior to SQL 2005 was READ COMMITTED.  One of the primary focus points with SNAPSHOT Isolation discussed in this chapter is with regards to the usage of triggers to enforce integrity and business rules.  Again, Alex goes to the extent to show how this could be a problem, how to reproduce the problem, what the expected results should have been, and he offers up a method to overcome the potential pitfalls.

I will throw one bit of warning here though.  Read carefully through the triggers and the verbiage that Alex has used.  There is a troubleshooting trigger which Alex uses to demonstrate a point.  As a preface to the trigger he states there is an embedded query in the trigger and that it should never be rolled to production.  If one reads this quickly, they might misread that and understand it to say that a trigger should never be used in production (that is a myth out there in DBA land already as it is).  I think an inline comment in the trigger explicitly pointing out the portion that should never be used in production would be worth the effort and type space.

As we read about the Merge feature, we are reintroduced to @@ROWCOUNT and an issue that can arise in a trigger.  We have an issue arise where the MERGE can cause unwanted behavior.  I like this section of the chapter.  Alex does a good job of demonstrating this feature and has intrigued me in regards to the Merge feature.

We see some good stuff once again in this chapter on Defensive Programming.  I recommend again that you read it.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

August 2010
« Jul   Sep »


  • @JanosBerke: Anyone has experience (good or bad) w/ jTDS and connection pooling? #sqlhelp
  • @NaineshBabariya: #sqlhelp I want to move my distributor server to a brand new server. Can some one help me with the steps having minimal downtime.
  • @arcticdba: One table, 1M rows/day, ever-increasing BIGINT+other col as key. Needs to be stored for 10 years. Partitioning? Other ideas? #sqlhelp
  • @MrACutler: Can I swap package conns for project conns in SSIS 2014? Ive conv a project to project-deployment but can only do the first package #sqlhelp
  • @SQLHA: @SivassqlG Assuming FCI and one of 'em, why do you care about setting preferred owners (which is how it's done)? #sqlhelp
  • @SQLHA: @SivassqlG FCI? AG? SQL Server clustering is not specific enough. And one instance? More than one instance? #sqlhelp
  • @SivassqlG: #sqlhelp how to set node priority in SQL server clustering..means we have 3 node cluster active-A,passive-B,passive-C
  • @soteark: Datalength() on xml variable shows half the size of the same xml data in an xml file without formatting. Why and how to solve? #sqlhelp
  • @BrentO: @EvoDBA No, databases in an AG can’t be restored. #SQLhelp
  • @BrentO: @zlthomps When you find yourself using multiple tweets for a question, try hitting up #SQLhelp

Welcome , today is Friday, November 27, 2015