title: Why is IS NOT NULL returning NULL values for a Varchar(max) in SQL Server?
body: <p><img src="https://i.stack.imgur.com/6Tm6i.jpg" alt="This is the query:"></p>
  
  <ol>
  <li>It looks like some NULL values are appearing in the list. </li>
  <li>Some NULL values <em>are</em> being filtered out by the query. I have checked.</li>
  <li>If I add <code>AND AdditionalFields = ''</code>, both these results are still returned</li>
  <li>AdditionalFields is a varchar(max)</li>
  <li>The database is SQL Server 10 with Compatibility Level = Sql Server 2005 (90)</li>
  <li>I am using Management Studio 2008</li>
  </ol>
  
  <p>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?!</p>
  
  <p><strong>EDIT:</strong>
  New datatype - hereby to be referred to as a "Numpty"</p>
  
  <p><strong>EDIT 2</strong>
  inserting the data into a temporary table turns Numpties into NULLS. (The result from this sql is 10)</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>EDIT 3</strong>
  And I can fix the data by running an update:</p>
  
  <pre><code>UPDATE Grants SET AdditionalFields = NULL
  WHERE AdditionalFields IS NOT NULL AND LEN(AdditionalFields) IS NULL
  </code></pre>
  
  <p>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?</p>
  
  <p><strong>EDIT 4</strong>
  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.</p>
  
  <p>Another example:
  <img src="https://i.stack.imgur.com/gF7z7.jpg" alt="enter image description here"></p>
  
  <p>Converting to varbinary
  <img src="https://i.stack.imgur.com/QnfC4.jpg" alt="enter image description here"></p>
  
  <p>Execution plan:
  <img src="https://i.stack.imgur.com/0mfDM.jpg" alt="Execution plan">
  <strong>EDIT 5:</strong> removed table definition - turned out to be not relevant in the end</p>
  
  <p><strong>EDIT 6</strong>
  Scripts to generate scripts for altering TEXT to VARCHAR(MAX) then update values to prevent bug and enhance performance</p>
  
  <pre><code>--Generate scripts to alter TEXT to VARCHAR(MAX)
  SELECT 'ALTER TABLE [' + tab.table_schema + '].[' + tab.table_name  + '] ALTER COLUMN [' + col.column_name + '] VARCHAR(MAX)' + CASE WHEN col.IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END + ' GO'
  FROM INFORMATION_SCHEMA.tables tab
  INNER JOIN INFORMATION_SCHEMA.COLUMNS col ON col.table_name = tab.table_name
            AND tab.table_schema = col.table_schema
            AND tab.table_catalog = col.table_catalog
  WHERE tab.table_type &lt;&gt; 'VIEW' and col.DATA_TYPE = 'text'
  
  --Generate scripts to set value to value in VARCHAR(MAX) fields
  SELECT 'UPDATE [' + tab.table_schema + '].[' + tab.table_name  + '] SET [' + col.column_name + '] = [' + col.column_name + ']'
  FROM INFORMATION_SCHEMA.tables tab
  INNER JOIN INFORMATION_SCHEMA.COLUMNS col ON col.table_name = tab.table_name
            AND tab.table_schema = col.table_schema
            AND tab.table_catalog = col.table_catalog
  WHERE tab.table_type &lt;&gt; 'VIEW' AND col.DATA_TYPE = 'varchar' and col.CHARACTER_MAXIMUM_LENGTH = -1
  </code></pre>