Re: NULL Changed to Empty String

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Alan Z. Scharf (ascharf@xxxxxxxxxxxxxx) writes:
I have SQL Server 2000 database where what were VARCHAR(200) column
NULL field values changed to empty strings, I.e. the rows are no
longer selectable with 'WHERE FieldName IS NULL', but are selectable
with 'WHERE LEN(FieldName) = 0'.

Is there any kind of circumstance that could cause this?

The column values in the particular rows in question were actually NULL
for four years until this past month.

The client swears they did nothing to this column in the table, although
they did add a couple of new columns to the table and change an existing
one from INT to VARCHAR(200).

If the value was NULL before and is a blank string now, it is because
someone changed it, consciously or inadverntly. SQL Server did not change
the value on a whim. It's as simple as that.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Best Practice for SQL Server Null Values / Empty Strings
    ... There are strong debates regarding whether or not nulls should ever be ... Wayne Snyder, MCDBA, SQL Server MVP ... > An empty string is a positive entry into the database. ... If you want to prevent empty strings from ...
    (microsoft.public.sqlserver.server)
  • Re: Subquery in Where clause
    ... The "AND LEN> 0" guarantees the case where both sides are empty strings won't return a result. ... Whether or not redundant conditions will "mess up the plan" is something you'll have to test on a case-by-case basis. ... A given predicate could cause the optimizer to use an index that might not be optimal for your entire query, ... "Expert SQL Server 2008 Encryption" ...
    (microsoft.public.sqlserver.programming)
  • Re: NULL Changed to Empty String
    ... I retested it, and as mentioned, this is the first time it happened in four years. ... "Michael Coles" wrote in message ... I have SQL Server 2000 database where what were VARCHARcolumn NULL field values changed to empty strings, I.e. the rows are no longer selectable with 'WHERE FieldName IS NULL', but are selectable with 'WHERE LEN= 0'. ...
    (microsoft.public.sqlserver.programming)
  • Re: NULL Changed to Empty String
    ... "Erland Sommarskog" wrote in message ... NULL field values changed to empty strings, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.programming)
  • Re: Each GROUP BY expression must contain at least one column that is not an outer reference
    ... Which is @FieldName in the select statement. ... where the string expression is ... Maybe you like to believe that SQL Server will guess that it should ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)