Hugo Kornelis (12/12/2009)
I don't have a SQL Server 2000 instance running, so I can only guess there....
My guess is that on SQL Server 2000, the engine assumes a zero length string for NULL.
After reading this, I became interested in 'zero-length strings'. I found one mention of varchar(0) in BOL (http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx):
Behavior Changes to Database Engine Features in SQL Server 2005
SQL Server 2000 behaviorA zero-length string or binary value that is used as the definition of a computed table column creates a column of type varchar(0), nvarchar(0), or varbinary(0).
After that I found a SQL Server 2000 instance on one of our development servers. Some interesting results were obtained.
SELECT 'abcde' AS a, 'abcde' + NULL AS b INTO TestTable;
go
EXEC sp_help TestTable;
go
DROP TABLE TestTable;
Column 'b' has type varchar(5).
Creating a table with a zero-length computed column:
CREATE TABLE TestTable (a VARCHAR(10), b AS SUBSTRING(a, 1, 0))
go
EXEC sp_help TestTable;
go
DROP TABLE TestTable;
Column 'b' has type varchar(0)!
Trying to create a copy of the above table:
CREATE TABLE TestTable (a VARCHAR(10), b AS SUBSTRING(a, 1, 0))
go
SELECT * INTO TestTable_Copy FROM TestTable
This code causes the error: "Msg 2731, Level 16, State 1, Line 1. Column 'b' has invalid width: 0."
So SQL Server 2000 sometimes uses varchar(0) data type. I was surprised 🙂