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

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 business demands on hold, and no hassle of traveling two time zones west. I am honored to ...

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

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 is bypassed. Let me provide some examples. I do not intend to provide a comprehensive list ...

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 will provide two examples, two rather common scenarios that may potentially cause loops to ...

  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 if you never use SET ROWCOUNT yourself, some legacy code can still use it – ...

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 programming is an approach that is designed to ensure high quality of ...

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 here. However, if you have ambiguities, SET will raise an error, but SELECT will not detect ...