Systems Engineering and RDBMS

Archive for July 18th, 2007

Empty String and integer data-type

Posted by decipherinfosys on July 18, 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:

create table demo_empty_string (col1 int null)
insert into demo_empty_string values (1)
insert into demo_empty_string values (”)
insert into demo_empty_string values (null)

select * from demo_empty_string

col1
———–
1
0
NULL

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.

Posted in SQL Server | 1 Comment »