Why does bcp output null when the column contains an empty string and empty string when the column is null?

Question

This struck me as really weird behaviour and I spent a while checking for bugs in my code before I found this

"out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string." (from http://msdn.microsoft.com/en-us/library/ms162802.aspx)

Obviously this allowed me to fix my problem but can anybody think of or does anybody know a reason why this is the case?

Answer 1

It's been some time, but I'm sure it's a backwards compatibility/legacy back to SQL Server 6.5

SQL Server 6.5 could not store empty string: there was always one space. This changed with SQL 7

So '' -> NULL and ' ' -> '' is correct from an ancient history perspective.