Hi,
Can anyone explain this to me?

Execute the following (on SQL Server 2000):

=============================================
create table mytable (mycol varchar(1) NULL)
go
insert into mytable values ('')
go
select ascii(mycol), len(mycol) from mytable
where mycol = ' '
go
=============================================
The SELECT statement returns:
NULL | 0

First of all...why on earth does the SELECT statement
return anything? Surely the WHERE clause should prevent it
from returning anything. I inserted an empty value, the
results of the SELECT show that the length is 0 and the
value is NULL, wo WHY OH WHY do I get a row back?


To make it weirder still, execute the following (N.B. I
have simply changed the field from NULLABLE to NON-
NULLABLE):
=============================================
create table mytable2 (mycol varchar(1) NOT NULL)
go
insert into mytable2 values ('')
go
select ascii(mycol), len(mycol) from mytable2
where mycol = ' '
go
=============================================
The SELECT statement returns:
NULL | 0

Why does it not fail on the INSERT? The SELECT statement
shows us that the value is NULL, so why did it let me
insert a NULL value into a NOT NULL column?


To me this seems very strange. I'm racking my brains to
come up with a plausible explanation but I can't think of
one.
Its as if it treats a NULL value to be the same as ''
and ' '

Weird! Any ideas?

cheers
Jamie

P.S. I know this is nothing to do with DTS but there isn't
a T-SQL newsgroup, not one that I can find anyway.