X-Received: by 10.224.17.140 with SMTP id s12mr27614587qaa.3.1362676608826; Thu, 07 Mar 2013 09:16:48 -0800 (PST)
X-Received: by 10.49.24.13 with SMTP id q13mr3486348qef.33.1362676608519; Thu, 07 Mar 2013 09:16:48 -0800 (PST)
Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!dd2no8040382qab.0!news-out.google.com!p7ni382qai.0!nntp.google.com!dd2no8040378qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Thu, 7 Mar 2013 09:16:48 -0800 (PST)
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=15.227.185.76; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u
NNTP-Posting-Host: 15.227.185.76
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <60db4087-d8ae-4cbf-9a40-531637ccef0e@googlegroups.com>
Subject: How to determine true contents of not null columns that display as empty
From: Mark D Powell <Mark.Powell2@hp.com>
Injection-Date: Thu, 07 Mar 2013 17:16:48 +0000
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Xref: csiph.com comp.databases.ms-sqlserver:1405

We have a table with a column that shows it must hold a value (nvarchar(7) =
not null), but when displayed always shows nothing.  Since "not null" is sp=
ecified that made the customer think the column contains one or more spaces=
.

I ran the following query (minus from/where) that returned results consiste=
nt with the customer view considering that datalength is supposed to return=
 Null for Null.  But when I added the ASCII function to see the value Null =
returned.  OK, so it appears that the column is being treated as a zero len=
gth string.

How can a "not null" colummn be empty?  Can anyone explain what I am seeing=
 and where specifically in the documentation I can read up on this?

select coalesce(acceptor,'SPACE'),ISNULL(acceptor,'NULL'),
       LEN(acceptor), DATALENGTH(acceptor),
       coalesce(DATALENGTH(acceptor),'X'), nullif(' ',acceptor) "NULLIF",
       ASCII(acceptor) "ASCII", REPLACE(acceptor,'','X') "NOSPACE",
       REPLACE(acceptor,' ','X') "SPACE", UNICODE(acceptor)
...

COALESCE  ISNULL  LEN	DATALENGTH	CK_DATAL	NULLIF	ASCII	NOSPACE	SPACE	UNICOD=
E
........  ......  0	0		0		NULL	NULL	.......	.....	NULL

Curious.
-- Mark D Powell --