为什么 IS NOT NULL 为 SQL Server 中的 Varchar(max) 返回 NULL 值? [英] Why is IS NOT NULL returning NULL values for a Varchar(max) in SQL Server?

问题描述

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

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

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

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?!

新数据类型 - 在此称为Numpty"

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

编辑 2将数据插入临时表会将 Numpties 变为 NULLS.(这个sql的结果是10)

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

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

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?

编辑 4我的数据库中还有 2 个其他字段，当字段为非空且 LEN(field) 为空时，它们都返回行的 varchar(max).所有这些字段都曾经是 TEXT 并更改为 VARCHAR(MAX).数据库也从 Sql Server 2005 移到了 2008.看起来我们默认关闭了 ANSI_PADDING 等.

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.

另一个例子:

转换为 varbinary

Converting to varbinary

执行计划:编辑 5: 删除了表定义 - 最后证明是不相关的

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

编辑 6用于生成将 TEXT 更改为 VARCHAR(MAX) 然后更新值以防止错误和提高性能的脚本的脚本

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

--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

推荐答案

我得到了一个示例代码来重现上述行为.当您有一个 TEXT 字段存储的值大于它可以容纳在一行中时，如果您之后将其设置为 NULL 并执行列转换为 <代码>VARCHAR(MAX).

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).

大值存储在单独的页面中.然后将此字段的值设置为 NULL.如果您现在将此列转换为 VARCHAR(MAX)，则 SQL Server 似乎无法正确处理.通常在 TEXT 到 VARCHAR(MAX) 的转换中，外部页面保持原样，但可能是因为它被设置为 NULL，列改变把事情搞砸了.

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.

更新:它似乎与 TEXT 列中的大值没有任何关系.短值显示相同的行为(扩展示例).因此，这只是通过 UPDATE 显式设置 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.

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) 列中的值.

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

UPDATE Test SET value = value

这似乎将值放在先前存储在外部页面中的行中.(参见参考:NTEXT vs NVARCHAR(MAX) inSQL 2005)

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)

这篇关于为什么 IS NOT NULL 为 SQL Server 中的 Varchar(max) 返回 NULL 值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！