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 back active transactions after timeouts:     public static class ...

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 function that imitates a subquery: CREATE FUNCTION dbo.BoxById ( @BoxId INT )RETURNS ...

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 pattern: CREATE TABLE dbo.Vehicles(    ID INT NOT ...

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 committed isolation using row versioning provides statement-level read consistency''. ...

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'))2010-03-28 00:00:00.0002010-03-30 00:00:00.000  ...

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 links with more information and more repro scripts, more or less in the order following ...

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. As promised, here are the links with more information and more repro scripts, more or less in ...

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 it is much better to allocate a Saturday for training and networking close to where I live, ...

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 defensive scenarios to discuss and describe. Many thanks to my longtime friend Hugo ...

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 are telling to the optimizer may be no longer relevant. In the previous post, Optimizing a ...