Systems Engineering and RDBMS

Archive for July 12th, 2007

Data Issues due to CHAR(0)

Posted by decipherinfosys on July 12, 2007

At one of the client sites (they use SQL Server as their RDBMS), in one of the columns that was declared as a variable character length column, the client application was dumping char(0) into the field whenever they did not know the value of the column.  This column was a mandatory column and because of bad programming, instead of doing validation checks and preventing the bad data from getting into the column, the developer had coded it to put in CHAR(0) instead.  What he really meant to do was to enter an empty string – even that is very bad practice – you can read more on that on our blog post here.

CHAR(0) is the NUL character (notice the missing L).  Just as you signal the end of input with the EOF constant, you signal the end of a string with the NUL character.  If all you are looking for is entering a space, use CHAR(32).  If the column had an empty string “embedded” in the description, that equates to CHAR(32) (Ascii value of 32 is space) and not CHAR(0).  Here is an example:

declare @table table (col1 varchar(30) not null)
insert into @table values (char(0))
insert into @table values (‘abcd’)

select count(1) from @table where col1 is null
———–
0

(1 row(s) affected)

select count(1) from @table where col1 = ”
———–
0

(1 row(s) affected)

select count(1) from @table where col1 = char(0)
———–
1

(1 row(s) affected)

select count(1) from @table where col1 is not null

———–
2

(1 row(s) affected)

This was fixed in the code and the existing data was put through the validation routine to help clean up the data.

Posted in SQL Server | Leave a Comment »

SQL Server 2008 Preview and Oracle 11g Docs

Posted by decipherinfosys on July 12, 2007

We had previously blogged about the June CTP download for the next version of SQL Server (code named Katmai) which is slated for release next year.
In case you want to get a preview of the new features coming in the next year’s release of SQL Server, there is an online free clinic from Microsoft:

Clinic 7045: What’s New in Microsoft® SQL Server™ 2008

With the releases of SQL Server 2005 (which was a major release with so many new features and changes to the existing ones) and now the next year release of SQL Server 2008, one should evaluate their migration strategies in case you still haven’t migrated to SQL Server 2005. It will thus be good to keep yourself up-to-date with the latest changes that are coming your way in the next release. Instead of just giving a small outline of each feature in this post (you can anyways get that from the link above), we will start covering the new features as of the June CTP in our blog posts.

On another note, Oracle has also released the documentation on their next release: Oracle 11g. You can access those here at Oracle’s site.

Posted in Oracle, SQL Server | Leave a Comment »