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:
- Snapshot Isolation
- 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.
