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.

1 Comment - Leave a comment
  1. […] This post was mentioned on Twitter by Jorge Segarra, Jason Brimhall. Jason Brimhall said: New post: Defensive Db Programming Chapter 02 ( […]

Leave a comment

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

July 2010
« Jun   Aug »


  • @SQLSoldier: @mvelic Yes, but then I pointed out that they were using nolock and SSIS isn't. The matching records were not committed. #sqlhelp #TrueStory
  • @mvelic: It's just maddening because this lookup has *always* worked in the past. It's just now deciding to not recognize matches. #sqlhelp
  • @mvelic: Has anyone just seen an SSIS Lookup fail to make matches? You know the matches exist, but it doesn't connect them and it fails? #sqlhelp
  • @banerjeeamit: @MattPgh No. Current processing report is not visible. This is visible in RunningJobs table but not the stats breakdown. #sqlhelp
  • @forhakim: #sqlhelp in Visual Studio SSDT is there a way to make it NOT show table designer, only the script, when I edit a table?
  • @MattPgh: @banerjeeamit Will the current report show up in ExecutionLog? whatever processing is happening did not finish yet. #sqlhelp
  • @banerjeeamit: @MattPgh Look at the time processing and rendering in the logging table: #sqlhelp
  • @banerjeeamit: @MattPgh Also, CPU time can be consumed due to rpt processing. This is available thru the ExecutionLogStorage table #sqlhelp
  • @banerjeeamit: @MattPgh Using XEvents or profiler u can see which stmt r CPU intensive? This wud gv u the cpu time consumed by the DB queries. #sqlhelp
  • @MattPgh: Is there a way to tell exactly what SSRS service is doing when it has CPU pegged to 100%? like a "what running" query in sql. #sqlhelp

Welcome , today is Saturday, October 10, 2015