November 2008 - Posts

• Try out sqltrace

  SQL Server MVP Erland Sommarskog is hosting a new utility on his web site. Apparently it is very useful. Enjoy! Read More... Posted Saturday, November 29, 2008 9:38 PM by Alexander Kuznetsov | 0 Comments

• Defensive database programming: eliminating IF statements.

  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. Read More... Posted Thursday, November 27, 2008 6:27 PM by Alexander Kuznetsov | 29 Comments Filed under: Database Programming, Defensive programming, SQL, SQL Server, Transact SQL

• Defensive database programming: fun with changing column widths.

  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 Read More... Posted Wednesday, November 19, 2008 2:34 PM by Alexander Kuznetsov | 14 Comments Filed under: Database Programming, Defensive programming, SQL Server, Transact SQL

• Avoid mixing old and new styles of error handling.

  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 Read More... Posted Saturday, November 15, 2008 11:27 PM by Alexander Kuznetsov | 16 Comments Filed under: Database Programming, Defensive programming, SQL Server, Transact SQL

• Defensive database programming: adding ESCAPE clauses.

  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 Read More... Posted Saturday, November 08, 2008 11:43 AM by Alexander Kuznetsov | 15 Comments Filed under: Database Programming, Defensive programming, SQL Server, Transact SQL