为什么IS NOT NULL在SQL Server中为Varchar（max）返回NULL值？

1. It looks like some NULL values are appearing in the list.
2. 看起来列表中出现了一些NULL值。
3. Some NULL values are being filtered out by the query. I have checked.
4. 查询会过滤掉一些NULL值。我检查过。
5. If I add AND AdditionalFields = '', both these results are still returned
6. 如果我添加AND AdditionalFields =''，则仍返回这两个结果
7. AdditionalFields is a varchar(max)
8. AdditionalFields是一个varchar（最大）
9. The database is SQL Server 10 with Compatibility Level = Sql Server 2005 (90)
10. 数据库是SQL Server 10，兼容级别= Sql Server 2005（90）
11. I am using Management Studio 2008
12. 我正在使用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?!

我似乎有空字符串，其长度为NULL，或NULL值等于空字符串。这是一个新的数据类型吗？！

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

编辑：新数据类型 - 特此称为“Numpty”

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

编辑2将数据插入临时表将Numpties转换为NULLS。 （这个sql的结果是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:

编辑3我可以通过运行更新来修复数据：

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.

编辑4我的数据库中有另外两个字段，varchar（max）在字段IS NOT NULL和LEN（字段）IS NULL时返回行。所有这些字段都是TEXT，并且更改为VARCHAR（MAX）。数据库也从Sql Server 2005迁移到2008.看起来我们默认情况下已经有ANSI_PADDING等。

Another example:

另一个例子：

Converting to varbinary

转换为varbinary

Execution plan: EDIT 5: removed table definition - turned out to be not relevant in the end

执行计划：编辑5：删除表定义 - 结果证明不相关

EDIT 6 Scripts to generate scripts for altering TEXT to VARCHAR(MAX) then update values to prevent bug and enhance performance

编辑6脚本生成脚本以将TEXT更改为VARCHAR（MAX）然后更新值以防止错误并提高性能

--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 <> '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 <> 'VIEW' AND col.DATA_TYPE = 'varchar' and col.CHARACTER_MAXIMUM_LENGTH = -1

5 个解决方案

#1

I got a sample code to reproduce the above behavior. The problem arises when you have a TEXT field that stores a value larger than it can fit in a row and if you set it afterwards to NULL and perform the column conversion to VARCHAR(MAX).

我有一个示例代码来重现上述行为。当您有一个TEXT字段存储一个大于它可以放在一行中的值并且之后将其设置为NULL并执行列转换为VARCHAR（MAX）时，就会出现问题。

The large value gets stored in a separate page. Then you set the value of this field to NULL. If you now convert this column to a VARCHAR(MAX), then SQL Server seems to not get it right. Typically on a TEXT to VARCHAR(MAX) conversion the external pages stay as they are, but maybe because it was set to NULL, the column altering messes things up.

大值存储在单独的页面中。然后将此字段的值设置为NULL。如果现在将此列转换为VARCHAR（MAX），则SQL Server似乎无法正确执行。通常在TEXT到VARCHAR（MAX）转换时，外部页面保持不变，但可能因为它被设置为NULL，该列改变了混乱。

Update: It doesn't seem to have anything to do with the large values in the TEXT column. Short values show the same behavior (extended sample). So it's just the explicit setting to NULL through an UPDATE and the conversion that matters.

更新：它似乎与TEXT列中的大值没有任何关系。短值显示相同的行为（扩展样本）。所以它只是通过UPDATE和重要的转换显式设置为NULL。

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL,
    [Value] [text] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO Test VALUES (1, 'test')
INSERT INTO Test VALUES (2, '')
INSERT INTO Test VALUES (3, NULL)
INSERT INTO Test VALUES (4, '012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789')
INSERT INTO Test VALUES (5, 'short string')
GO

update test SET value = null where ID = 4
update test SET value = null where ID = 5
GO

ALTER TABLE test ALTER COLUMN value varchar(max)
GO

select id, value, len(value) as length
from test
where value is not null
GO

The result is:

结果是：

1   test    4
2           0
4   NULL    NULL
5   NULL    NULL

An easy fix for this problem would be to reassign the values in the VARCHAR(MAX) columns.

解决此问题的一个简单方法是重新分配VARCHAR（MAX）列中的值。

UPDATE Test SET value = value

This seems to put the values in the rows that were previously stored in external pages. (See for reference: NTEXT vs NVARCHAR(MAX) in SQL 2005)

这似乎将值放在先前存储在外部页面中的行中。 （参见：SQL 2005中的NTEXT与NVARCHAR（MAX））

#2

This is just an addition to McSim's answer using SQL Server Internals Viewer to look at the individual stages.

这只是McSim使用SQL Server Internals Viewer查看各个阶段的答案的补充。

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL PRIMARY KEY ,
    [Value] [text] NULL)


INSERT INTO Test VALUES (1, '')

Row after initial insert

Text value after initial insert

update [Test] SET [Value] = null 

Row after update to NULL

This is identical to the row shown earlier so I haven't repeated the screenshot. Specifically the NULL_BITMAP does not get updated to reflect the new NULL value.

这与前面显示的行相同，所以我没有重复截图。具体而言，NULL_BITMAP不会更新以反映新的NULL值。

Text value after update to NULL

The Type bits have changed and Internals Viewer shows this as no longer containing a value for the Data column.

Type类已更改，Internals Viewer将其显示为不再包含Data列的值。

At this point running the following correctly returns no rows

此时正确运行以下命令不会返回任何行

SET STATISTICS IO ON
select [Id]
from [Test]
where [Value] is not null

So SQL Server must follow the text pointer and look at the value there to determine NULL-ability.

因此，SQL Server必须遵循文本指针并查看其中的值以确定NULL-ability。

ALTER TABLE [Test] ALTER COLUMN [Value] varchar(max)

This is a metadata only change. Both the inrow and out of row data remain unchanged.

这是仅元数据的更改。 inrow和out行数据都保持不变。

However at this point running the following incorrectly returns the row.

但是，此时运行以下错误地返回该行。

SET STATISTICS IO ON
select [Id]
from [Test]
where [Value] is not null

The output of STATISTICS IO

STATISTICS IO的输出

Scan count 1, logical reads 2, ... lob logical reads 1

扫描计数1，逻辑读取2，... lob逻辑读取1

shows that it still does actually follow the text pointer but presumably in the varchar(max) case there must be a different code path that incorrectly ends up taking the value from the NULL_BITMAP regardless (the value of which has never been updated since the initial insert).

表明它仍然确实遵循文本指针，但可能在varchar（max）情况下必须有一个不同的代码路径，错误地最终从NULL_BITMAP获取值（从初始插入以来，其值从未更新过） ）。

#3

As others have pointed out this result is utterly impossible.

正如其他人指出的那样，这个结果是完全不可能的。

1. Please post a screenshot of the actual execution plan.
2. 请发布实际执行计划的屏幕截图。
3. Please run dbcc checkdb and post the error messages, if any.
4. 请运行dbcc checkdb并发布错误消息（如果有）。

(2) is actually my favorite right now.

（2）现在是我最喜欢的。

#4

Colin:

科林：

I'm pretty sure all this is happening because of the database conversion. Since you need to solve this thing ASAP, my suggestion is to guarantee that your AdditionalFields data is ok first, and try to understand why this happens after:

由于数据库转换，我很确定这一切都在发生。由于您需要尽快解决这个问题，我的建议是保证您的AdditionalFields数据首先是正常的，并尝试理解为什么会发生这种情况：

1. Do a backup;
2. 做一个备份;

3. Run this T-SQL:

   运行此T-SQL：

   update grants
   set AdditionalFields = ltrim(rtrim(isnull(AdditionalFields,'')))
   

The isnull function will transform your null values in empty strings, and the left/right trim should guarantee that even fields with more than one space will have the same value after.

isnull函数将在空字符串中转换空值，左/右修剪应保证具有多个空格的偶数字段之后将具有相同的值。

Could you run this and feedback us later with the results?

你可以运行这个并在结果后反馈我们吗？

Best regards

最好的祝福

#5

I suspect the word NULL is neing stored in the db, use select * from blah where mycolumn = 'NULL'

我怀疑单词NULL是存储在db中的，使用select * from blah where mycolumn ='NULL'