Browse by Tags

• Data Integrity
• Database Programming
• Database Testing
• Deadlocks
• Infinite loops
• query performance
• snapshot isolation
• SQL
• SQL Server
• Stress Testing

• Yet another use of OUTER APPLY in defensive programming

  When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem. Prerequisites All we need is the following mock Read More... Posted Wednesday, June 6, 2012 5:58 PM by Alexander Kuznetsov | 2 Comments Filed under: Transact SQL, Defensive programming

• "Trusted" Foreign Keys Allow Orphans, Reject Valid Child Rows

  In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save. False Negatives The following tables implement a very common type/subtype Read More... Posted Monday, October 17, 2011 1:15 PM by Alexander Kuznetsov | 11 Comments Filed under: Transact SQL, Defensive programming, Data Integrity

• Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent.

  When a select uses scalar or multi-statement UDFs under READ_COMMITTED_SNAPSHOT, we might not get consistent results as of the time our select began - I will provide simple repro scripts. At the time of this writing MSDN clearly states the following: Read More... Posted Tuesday, August 2, 2011 9:33 PM by Alexander Kuznetsov | 2 Comments Filed under: Database Programming, Transact SQL, Defensive programming

• Math with Months Is Not Commutative

  In other words, if we add a month, then subtract a month, we might not get back to the date we started from. For example: SELECT DATEADD ( MONTH , 1 , DATEADD ( MONTH , - 1 , '20100330' )) , DATEADD ( MONTH , - 1 , DATEADD ( MONTH , 1 , '20100330' )) Read More... Posted Monday, November 29, 2010 5:58 PM by Alexander Kuznetsov | 6 Comments Filed under: Transact SQL, Defensive programming

• My book is complete, many thanks to Hugo, Tony, and many others!

  My book is finished. A couple years ago I googled up "Defensive Database Programming", and came up with nothing. Naturally, I started filling up the void. Although the book is complete, I am planning to continue my research; I would appreciate any other Read More... Posted Friday, June 18, 2010 3:22 PM by Alexander Kuznetsov | 14 Comments Filed under: Transact SQL, Defensive programming

• Yet another example of defensive query optimization

  My second take at the same problem I blogged about yesterday: To optimize a query, we frequently have to explicitly tell optimizer some information which it does not realize by itself. Short term, this works, but long term we run the risk that what we Read More... Posted Thursday, February 18, 2010 3:11 PM by Alexander Kuznetsov | 1 Comments Filed under: Transact SQL, Defensive programming, query performance

• Optimizing a query, then documenting assumptions in a unit test.

  To optimize a query, we frequently have to explicitly tell optimizer some information which it does not realize by itself. Short term, this works, but long term we run the risk that what we are telling to the optimizer may be no longer relevant. This Read More... Posted Wednesday, February 17, 2010 5:02 PM by Alexander Kuznetsov | 5 Comments Filed under: Transact SQL, Defensive programming

• Don't swap horses in midstream.

  We can begin a transaction under snapshot isolation, but we cannot switch to it in the middle of an outstanding transaction. For example, the following procedure looks good and passes a smoke test: CREATE PROCEDURE dbo.SelectCountry @CountrySymbol CHAR Read More... Posted Monday, February 1, 2010 2:07 PM by Alexander Kuznetsov | 2 Comments Filed under: Transact SQL, Defensive programming, snapshot isolation

• When acquiring locks in the same order is not possible or not feasible.

  To avoid deadlocks, one of the most common recommendations is "to acquire locks in the same order" or "access objects in the same order". Clearly this makes perfect sense, but is it always feasible? Is it always possible? I keep encountering cases when Read More... Posted Friday, January 15, 2010 4:18 PM by Alexander Kuznetsov | 6 Comments Filed under: Transact SQL, Defensive programming, Deadlocks

• T-SQL Tuesday #002: patterns that do not work as expected.

  Neither UPDATE … IF (@@ROWCOUNT = 0) INSERT nor IF EXISTS(...) UPDATE ELSE INSERT patterns work as expected under high concurrency. Both may fail. Both may fail very frequently. MERGE is the king - it holds up much better.Let us do some stress testing Read More... Posted Tuesday, January 12, 2010 9:38 AM by Alexander Kuznetsov | 14 Comments Filed under: Transact SQL, Defensive programming, Stress Testing

• Your TRY block may fail, and your CATCH block may be bypassed.

  Some T-SQL code is written under the assumption that either a TRY block successfully completes or a CATCH block is invoked. Most likely, this is the case. However, there is a third, although rare, possibility – the TRY block may fail, and the CATCH one Read More... Posted Wednesday, May 13, 2009 9:11 PM by Alexander Kuznetsov | 10 Comments Filed under: SQL Server, Database Programming, Transact SQL, Defensive programming

• Avoiding infinite loops. Part One.

  Although there are many discussions about which kind of cursor or loop performs the best, there is no doubt which loops perform the worst – the infinite ones of course. Whenever you write a loop, you need to make sure that it never runs infinitely. I Read More... Posted Monday, April 20, 2009 11:11 PM by Alexander Kuznetsov | 8 Comments Filed under: SQL Server, Database Programming, Transact SQL, Defensive programming, Infinite loops

• Defensive database programming: fun with ROWCOUNT

  I have written up two examples when a SET ROWCOUNT command breaks a seemingly working stored procedure or trigger. Note that currently the best practice is to use TOP clause instead of SET ROWCOUNT, which is deprecated in SQL Server 2008. However, even Read More... Posted Saturday, March 21, 2009 10:59 PM by Alexander Kuznetsov | 8 Comments Filed under: SQL Server, Database Programming, Transact SQL, Defensive programming

• Summarizing previous posts about defensive database programming

  I have been posting examples of defensive database programming for some time now. I am by no means done with this topic, there is much more to it. Yet this time I would like to skip concrete examples and write up a long overdue introduction. Defensive Read More... Posted Sunday, March 8, 2009 9:33 PM by Alexander Kuznetsov | 5 Comments Filed under: SQL Server, Database Programming, Transact SQL, Defensive programming

• Defensive database programming: SET vs. SELECT.

  Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before. Assigning multiple values via SELECT performs better , and you don’t have to repeat your code several times, as described by Tony Rogerson here and Read More... Posted Sunday, January 25, 2009 1:40 PM by Alexander Kuznetsov | 12 Comments Filed under: SQL Server, Database Programming, Transact SQL, Defensive programming