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

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

Suppose that you need to implement the following logic: IF(row exists)   Update the row ELSE  Insert a new row. If you already are on 2008, you should use MERGE command, and you don’t need to read this post. Prior to 2008 this logic has to be implemented using UPDATE and INSERT commands. I will stress test several ...

It is well known that UPDATE ... FROM command does not detect ambiguities. Also it well known that ANSI standard UPDATE may perform very poorly and may be difficult to maintain, because it does not adhere to the fundamental DRY (Don’t Repeat Yourself) principle. I will tweak UPDATE ... FROM in three different ways so that you can detect or ...

The following pattern is quite common in database programming: IF EXISTS(some query) BEGIN   DO SOMETHING; END When such code runs in high concurrency situations, it may not work as expected. I will provide a repro when such logic fails 40% of the time. The following script provides a test table and attempts to ...

In Transact SQL you can assign a 10-character value to a VARCHAR(5) variable, as follows:   DECLARE @c VARCHAR(5); SET @c='1234567890'; PRINT @c 12345   The value will be silently truncated without raising an error. Because of this behavior it may be very easy to make mistakes. For example, consider the following table ...