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

If your data has a small percentage of duplicates, then IGNORE_DUP_KEY may speed up your inserts. For larger amounts of duplicates, IGNORE_DUP_KEY may slow them down significantly. I set up two tables, stripping down all the irrelevant details, as follows:   CREATE TABLE t1(n INT NOT NULL PRIMARY KEY) GO CREATE TABLE ...

<Denis Gobo’s mode on> Suppose you have an empty table:   SELECT COUNT(*) FROM SampleTable --- 0   What would be the result of the following query:   DBCC CHECKIDENT('SampleTable', RESEED, 1) INSERT SampleTable(j) SELECT 1 SELECT SCOPE_IDENTITY()   <Denis Gobo’s mode ...