Sounds trivial? Right, but different flavors of this myth still persist. Yesterday I noticed a thread on stackoverflow discussing SELECT TOP without a where clause, where it was suggested that ''The order will be defined based on the clustered key in that table.'' ...

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

We can tuck any logical expressions in CHECK constraints, but all foreign keys are currently capable of right now is to verify that one or more column values are equal. In many cases it would be very useful to have foreign keys use more complex logical expressions. Also in some cases the ability to create constraints on indexed views would ...

Suppose that you need to enforce the following business rule: contracts cannot be changed after you have started working on them (let us assume that that particular business operates in the perfect world).  You can use a ROWVERSION column, a persisted computed one, and a foreign key constraint to implement this rule, and I ...

When you process your rows one by one in a loop, you need to make sure that your loop never runs infinitely. I will describe three scenarios with possible infinite loops. There have been many articles discussing feasibility of loops vs. set-based solutions – I will not repeat any of these arguments. Let us assume that you need to use some ...

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

Selects under READ COMMITTED may return incorrect results if the data they select is being modified at the same time. I will provide a repro script which on my laptop returns incorrect results in more than 90% cases. Also I will provide a similar repro script which runs under REPEATABLE READ but still returns incorrect results in more than ...

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

Apparently for high isolation levels some heap tables may be more prone to deadlocks than identical tables with clustered indexes. I have a simple repro script which successfully completes if a table has a clustered index but embraces in a deadlock if it runs against a heap. Here is the table:   CREATE TABLE dbo.NarrowTable(ID INT ...

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