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  ...

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 ...

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 is when unit test shine - they allow us to document the assumptions which we are telling to the ...

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 ...

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 I cannot follow this advice. If I store an object in one parent table and one or more child ...

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 and see for ourselves. Here is the table we shall be ...