Well I have just read a post by Jonathan Kehayias named SQL Tip: Keep your Presentation Logic in your Presentation LayerI cannot say I completely agree with it. Of course if all the following assumptions are true:You already have a presentation layerYou are using only one presentation layerYour client is good at formatting dataThe ...

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

Stored procedures using old-style error handling and savepoints may not work as intended when they are used together with TRY … CATCH blocks. I will provide some examples. This post continues the series on defensive database programming.   Avoid calling old-style stored procedures from TRY blocks.   Stored procedures ...

In most cases LIKE conditions should by followed by ESCAPE clauses. Let me give you an example. Consider the following table, sample data, and stored procedure:  CREATE TABLE Data.Messages(Subject VARCHAR(30), Body VARCHAR(100)); GO INSERT INTO Data.Messages(Subject, Body) SELECT 'Next release delayed', 'Still fixing bugs' ...

If you do not qualify columns in your query, which means that you do not specify from which tables your columns come, you may have problems if the database schema changes. For example, consider the following sample tables and a select query: CREATE TABLE Data.Shipments(Barcode VARCHAR(30), SomeOtherData VARCHAR(100))GOINSERT INTO ...

The behavior of NOT IN clause may be confusing and as such it needs some explanations. Consider the following query: SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)   Although there are more than a thousand distinct last names in AdventureWorks.Person.Contact, the query returns ...