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.

Defensive Db Programming Chapter 03

Chapter 3 of the Defensive Database Programming Book by Alex Kuznetsov teaches us about how to “survive” changes to database objects.  Alex brings to light how simple changes to the underlying schema could be damaging to performance and accuracy of the code that touches the objects to be changed.

Three main focal points are discussed in this chapter.

  • Changes to the Primary or Unique Keys
  • Changes to Stored Procedure signatures
  • Changes to Columns

A key lesson is outlined in subtle terms at the beginning of the chapter.  If you have assumptions based on any of the above focal points, then those assumptions must be documented!  If this information or the assumptions are not documented, how can the changes made be properly tested?  All assumptions must be tested and validated.  If changes are made to the schema, any prior assumptions for the code touching the affected column, key, or signature must be reassessed, documented and validated.

Alex also brings up a a good point about Unit testing and how that can help speed things along when validating these assumptions.  This also provides a gateway to a level of documentation for those assumptions.

In addition to Unit testing, Alex suggests a counter measure of using @@ROWCOUNT to ensure that only 1 row is updated in update procedures.  If the rowcount is different than the expected 1 row, then the transaction is rolled back.  In his samples for this, Alex uses IF blocks.  I would have liked to see a TRY…Catch implementation as well.  This method is less preferable to the Unit test.  The reasons for that are explained in the chapter – have a read and find out why.

Further along in this chapter we learn another important lesson and a good practice to be using.  When calling a stored procedure that has parameters, use the parameter names when calling the stored procedure.

From here, there are several other recommendations in this chapter worth reading.  We see more best practices and further discussion elaborating particular methods over other methods.  There are several good lessons to be learned from this chapter and to find the rest of them – you will have to read it.  Enjoy reading!

Defensive Db Programming Chapter 02

This is the review of the second chapter of the book Defensive Database Programming.  The title of this chapter is “Code Vulnerabilities Due to SQL Server MisConceptions.”

This chapter examines three common misconceptions:

  1. WHERE clause conditions will always be evaluated in the same order
  2. SET and SELECT always change the values of variables
  3. Data will be returned in some “natural order”

Another misconception is that DBAs and Developers know these misconceptions.  As frequently as we are reminded of these things, it is not enough and we need to continue to remind ourselves about these misconceptions as well as teach others the same thing.

As was shown in the first chapter, we have been given some samples of how these statements are proved invalid.  Due to the proof of the misconception, we are also given some alternatives to code against them.

This is the kind of information that should be required reading for anybody who needs to write a stored procedure or a script that touches a database.  Even for seasoned professionals, the samples laid out in this chapter need to be examined.  I think most of us could find an application in our current environments where we could apply these principles right away.

In this chapter we also see another use for the “Numbers” table.  This chapter is also rather easy to read and written well.  I hope you enjoy reading it as well.

Defensive Database Programming Chapter 01

Today I am finally getting around to the review of the first chapter as promised.  I talked about the book a bit in the first post on the subject, which can be found here.  This book was written by Alex Kuznetsov (blog).

In this chapter, Alex discusses some basic defensive programming techniques.  That, in fact, is essentially the name of the chapter.  This is a pretty lengthy chapter – and with good reason.  Alex is laying the groundwork in this chapter with some basic samples and pretty much giving an introduction to the book.  The code samples are straight forward and designed to illustrate his point quite well.

During my current reading, a good takeaway from this chapter is the discussion on Rowcount.  For me, it serves as a prime reminder of the deprecation status as of SQL Server 2008.  If you are using SET ROWCOUNT, stop and change your practices.  Alex suggests using TOP in lieu of SET ROWCOUNT.

With each case that Alex presents as a potential problem when building your SQL code, he offers up a solution on how to avoid that problem.  The composition of using problems and providing solutions is quite handy.

And in Alex’s own words (Chapter 1 Summary, Page 59)

“It is vital that you understand and document the assumptions that underpin your implementation, test
them to ensure their validity, and eliminate them if they are not.”

SQL Book Review

Categories: News, Professional
Comments: 5 Comments
Published on: May 21, 2010

Recently I received a new opportunity via email.  Steve Jones at SQLServerCentral sent me an email to see if I would be interested in helping review a book and write about it on my blog.  I told Steve I would be happy to help out.

The book is titled Defensive Database Programming and is currently available at RedGate.  This book was authored by Alex Kuznetsov and is 10 chapters long and376 pages.  I will be writing between 1 and 10 articles concerning this book.  I would like to dive into the book and check things out and give it a good fair shake.

After reading the author Bio, I added Alex to my blog roll.  I added him if for nothing more than he runs Ultra marathons and is a SQL Server database guy.  You can find Alex’s blog here.

One last tidbit for now, the book had a technical review performed by Hugo Kornelis.

page 2 of 2»

August 2019
« Jul    

Welcome , today is Saturday, August 24, 2019