为什么在SQL Server中使用IS NOT NULL对于Varchar(max)返回NULL值？

Question

1. 看起来列表中出现了一些 NULL 值。
2. 查询已经过滤掉了一些 NULL 值。我已经检查过了。
3. 如果我添加 AND AdditionalFields = ''，这两个结果仍然会被返回。
4. AdditionalFields 是一个 varchar(max) 类型。
5. 数据库是 SQL Server 10，并且兼容级别为 Sql Server 2005 (90)。
6. 我正在使用 Management Studio 2008。

我发现有一些空字符串的长度为 NULL，或者 NULL 值等于一个空字符串。这是一种新的数据类型吗？！

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

编辑 2： 将数据插入临时表后，Numpty 变成了 NULL。 （此 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

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

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

所以这让我认为字段必须包含某些内容，而不是模式定义的问题。但具体是什么？我该如何防止它再次出现？ 编辑4 我的数据库中还有另外2个varchar(max)字段，它们在该字段IS NOT NULL AND LEN(field) IS NULL时返回行。所有这些字段曾经都是TEXT类型，并且已更改为VARCHAR(MAX)。数据库也从Sql Server 2005移至2008. 看起来我们默认关闭了ANSI_PADDING等。

另一个例子： 转换为varbinary 执行计划： 编辑5：删除表定义 - 最后结果证明与此无关 编辑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

- Colin

---

你的查询是否完全按原样发布？在WHERE子句中是否有可能拼写错误AdditionalFields，但在SELECT列表中没有（这将导致对另一个字段进行过滤）？类似于WHERE 'g.AdditionalFields' IS NOT NULL的语句会产生类似的行为（因为您不是在过滤字段而是在过滤字符串常量）。 - Quassnoi

作为调试提示：只需输出 PK，以便查看生成结果的行。 - MicSim

顺便说一下，您可以发布整个SSMS窗口的屏幕截图，这样我们就可以更轻松地验证您的说法。 - Lieven Keersmaekers

在SSMS中，按下CTRL+M，我仍然建议包括“授权”定义，以避免不得不重新开始。首先，“它有相同的问题”，什么列？什么查询？ - Lieven Keersmaekers

我已创建了一个联接项目来解决这个问题。尽管我还没有测试这个问题是否在SQL Server 2012中发生。 - Martin Smith

Answer 1

我得到了一个示例代码来复现上述行为。当您拥有一个存储值大于可以适合一行的TEXT字段，并且如果您随后将其设置为NULL并执行列转换为VARCHAR（MAX）时，问题就会出现。

大值被存储在单独的页面中。然后，您将此字段的值设置为NULL。如果现在将此列转换为VARCHAR（MAX），那么SQL Server似乎无法正确理解。通常，在 TEXT 到VARCHAR（MAX）转换上，外部页面保持不变，但可能由于它设置为 NULL ，列修改搞砸了事情。

更新：这似乎与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

结果是：

1   test    4
2           0
4   NULL    NULL
5   NULL    NULL

解决这个问题的简单方法是重新分配VARCHAR(MAX)列中的值。

UPDATE Test SET value = value

这似乎将之前存储在外部页面的行中的值放入了表格中。(参考：SQL 2005中的NTEXT vs NVARCHAR(MAX))

- MicSim

---

这里的问题在于Text数据类型；你需要以不同的方式处理它们，其中datalength(Value)=0。详细信息请参见https://dev59.com/HnVD5IYBdhLWcg3wRpeX。 - u07ch

@u07ch：仔细阅读问题。它不是关于TEXT数据类型的，而是关于VARCHAR(MAX) NULL值在使用IS NOT NULL检查时返回TRUE的问题。 - MicSim

提供一个repo加1分。显式插入NULL的行是该列唯一设置NULL_BITMAP为1的行。对于text数据类型，我之前注意到更新为NULL并不会更新位图，但似乎会在指针或指向的结构中设置某种标志。如果在转换为max之前执行“select [Id]，[Value] from [Test] where [Value] is not null”，则会返回正确的结果，但NULL_BITMAP仍然为零，因此必须在其他地方查找确定是否为空。 - Martin Smith

非常棒的故障排除 MicSim：您将问题缩小到一组特定的步骤，重现了不希望出现的行为，并找到了解释，恭喜！ - tcbrazil

太棒了！谢谢。最好去修复我们数据库中所有的文本和Varchar(max)字段... - Colin

Answer 2

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

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


INSERT INTO Test VALUES (1, '')

插入行之后

插入文本值之后

update [Test] SET [Value] = null 

更新为 NULL 后的行

这与之前显示的行完全相同，因此我没有重复截图。具体来说，NULL_BITMAP 不会被更新以反映新的 NULL 值。

更新为 NULL 后的文本值

Type 位已更改，Internals Viewer 显示它不再包含 Data 列的值。

此时运行以下命令将正确地返回零行：

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

因此，SQL Server必须跟随文本指针并查看那里的值以确定是否为空。

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

这是仅涉及元数据的更改。行内和行外数据保持不变。

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

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

STATISTICS IO的输出结果如下：

扫描次数1，逻辑读取次数2，... 大型对象逻辑读取次数1

该结果显示，在varchar(max)情况下，它实际上仍然遵循文本指针，但可能存在不同的代码路径，错误地从NULL_BITMAP中取值（其值自插入初始以来从未更新）。

- Martin Smith

---

非常感谢Martin。如果我在服务器上有权限做这件事，我们本可以更早完成。我们复制了一个备份，但昨天我没有时间尝试。 - Colin

Answer 3

Colin：

我相信这一切都是由于数据库转换引起的。既然你需要尽快解决这个问题，我的建议是先确保你的AdditionalFields数据没有问题，然后再尝试理解为什么会出现这种情况：

1. 进行备份；

2. 运行此 T-SQL：

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

isnull函数将把您的空值转换成空字符串，而左/右修剪应该保证即使字段有多个空格，在这之后也会拥有相同的值。

您能运行这个程序并在稍后反馈结果吗？

最好的问候

- tcbrazil

---

我已经知道可以通过运行更新将numpties转换为nulls（我在事务内运行了上面的更新），所以我不确定这会告诉我们更多信息。我想保留数据以帮助调查，而且问题还没有导致任何重大问题。我没有足够的权限查看底层页面数据，因此我认为我将尝试备份并将数据库文件拉到我拥有管理员权限的系统上。现在不在工作，所以明天再关注... - Colin

Answer 4

我怀疑数据库中存储了NULL这个单词，请使用select * from blah where mycolumn = 'NULL'进行查询。

- drew

Answer 5

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

1. 请发布实际执行计划的截图。
2. 请运行dbcc checkdb并发布错误消息（如果有）。

（2）目前是我最喜欢的。

- usr

---

CHECKDB 在数据库中未发现任何分配错误和一致性错误。 - Colin