Archive for July 18th, 2007

Ran into an issue yesterday which was caused by the way the application was treating the non-availability of the data. The client was using SQL Server 2005 (SP2) and when the data was not available for a particular attribute of a table, the application was passing in an empty string for it ( ” ) instead of passing in NULL. We have blogged before why using empty strings is a bad practice and the issues that it can create. In this case, the application was trying to insert that empty string value in an integer data-type column. That should have resulted into an error condition since a string is being input into an integer data-type column. However, since it was an empty string, the database engine converted that to a 0 and inserted it into the table. The only reason why this was caught was because this column happened to be in a child table which was referencing the parent table using this integer column and the value of 0 was not present in the parent table. Let’s see this using an example:

The conversion of the empty string to a value of 0 occurred here. Such issues are difficult to catch – say this was happening on a column that was not part of the foreign key relationship and you were using 0 to represent something in your system. In that case, the difference between 0 and null (in this case representing the non-availability of the data) would have been lost. In the above scenario, the application code was fixed to insert null instead of the empty string.