Systems Engineering and RDBMS

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.

One Response to “Empty String and integer data-type”

  1. […] about the side effects of using a zero length string for an integer  data type column before – here.  Today, we saw a similar issue at a client site – this time with a datetime data type column.  […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: