This is the query:

  1. It looks like some NULL values are appearing in the list.
  2. Some NULL values are being filtered out by the query. I have checked.
  3. If I add AND AdditionalFields = '', both these results are still returned
  4. AdditionalFields is a varchar(max)
  5. The database is SQL Server 10 with Compatibility Level = Sql Server 2005 (90)
  6. I am using Management Studio 2008

I appear to have empty strings whose length is NULL, or NULL values that are equal to an empty string. Is this a new datatype?!

EDIT: New datatype - hereby to be referred to as a "Numpty"

EDIT 2 inserting the data into a temporary table turns Numpties into NULLS. (The result from this sql is 10)

CREATE TABLE #temp(ID uniqueidentifier , Value varchar(max))

INSERT INTO #temp 
SELECT top 10 g.ID, g.AdditionalFields
FROM grants g 
WHERE g.AdditionalFields IS NOT NULL AND LEN(g.AdditionalFields) IS NULL

SELECT COUNT(*) FROM #temp WHERE Value is null

DROP TABLE #temp

EDIT 3 And I can fix the data by running an update:

UPDATE Grants SET AdditionalFields = NULL
WHERE AdditionalFields IS NOT NULL AND LEN(AdditionalFields) IS NULL

So that makes me think the fields must contain something, rather than some problem with the schema definition. But what is it? And how do I stop it ever coming back?

EDIT 4 There are 2 other fields in my database, both varchar(max) that return rows when the field IS NOT NULL AND LEN(field) IS NULL. All these fields were once TEXT and were changed to VARCHAR(MAX). The database was also moved from Sql Server 2005 to 2008. It looks like we've got ANSI_PADDING etc OFF by default.

Another example: enter image description here

sql-server sql-server-2005
Edit
Report