Alexander Kuznetsov : Defensive programming

The client code that handles timeouts

Alexander Kuznetsov — Thu, 10 Jan 2013 21:05:00 GMT

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests. Implementation The following class extends SqlCommand and rolls...(read more)

Yet another use of OUTER APPLY in defensive programming

Alexander Kuznetsov — Wed, 06 Jun 2012 21:58:00 GMT

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)

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

Alexander Kuznetsov — Mon, 17 Oct 2011 17:15:00 GMT

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)

Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent.

Alexander Kuznetsov — Wed, 03 Aug 2011 01:33:00 GMT

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)

Math with Months Is Not Commutative

Alexander Kuznetsov — Mon, 29 Nov 2010 22:58:00 GMT

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)

Additional links for my presentation on defensive database programming.

Alexander Kuznetsov — Sun, 19 Sep 2010 18:29:00 GMT

Yesterday I delivered a session named "Develop T-SQL defensively" at East Iowa SQL Saturday. Thanks to those who attended, and many thanks to Michelle Ufford, Chris Leonard, Ed Leighton-Dick, and other volunteers, who organized the event. Here are the...(read more)

Repro scripts and more info for my presentation on concurrency

Alexander Kuznetsov — Sun, 19 Sep 2010 18:10:00 GMT

Yesterday I delivered a session named "Developing T-SQL to survive concurrency" at East Iowa SQL Saturday. Thanks to everyone who attended, and thanks to Michelle Ufford, Chris Leonard, Ed Leighton-Dick, and other volunteers, who organized the event....(read more)

Speaking at SQL Saturday #50 in Iowa City, IA, and the recent disturbance in the Force.

Alexander Kuznetsov — Sun, 29 Aug 2010 15:29:00 GMT

In a few weeks I will be meeting a lot of old friends and new folks at SQL Saturday #50 in Iowa City . I'll do two presentations: on defensive database programming and on coding for high concurrency. I really like this whole SQL Saturday thing. For me...(read more)

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

Alexander Kuznetsov — Fri, 18 Jun 2010 19:22:00 GMT

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)

Yet another example of defensive query optimization

Alexander Kuznetsov — Thu, 18 Feb 2010 20:11:00 GMT

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)

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

Alexander Kuznetsov — Wed, 17 Feb 2010 22:02:00 GMT

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)

SQL Saturday coming to Chicago on Apr 17!

Alexander Kuznetsov — Thu, 11 Feb 2010 22:08:00 GMT

The schedule is so interesting that I want to get a clone and be in two sessions at the same time. I think of this event as our local PASS Summit Lite – all the benefits like attending sessions and meeting interesting people, but without having to put...(read more)

Don't swap horses in midstream.

Alexander Kuznetsov — Mon, 01 Feb 2010 19:07:00 GMT

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)

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

Alexander Kuznetsov — Fri, 15 Jan 2010 21:18:00 GMT

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)

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

Alexander Kuznetsov — Tue, 12 Jan 2010 14:38:00 GMT

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)