Databases
StackOverflow2013
Postsdbo
POWhy is IS NOT NULL returning NULL values for a Varchar(max) in SQL Server?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy is IS NOT NULL returning NULL values for a Varchar(max) in SQL Server?
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload