Почему IS NOT NULL возвращает значения NULL для Varchar (max) в SQL Server?

Похоже, что в списке появляются некоторые значения NULL.
Некоторые значения NULL отфильтровываются по запросу. Я проверил.
Если я добавлю AND AdditionalFields = '', оба этих результата будут возвращены
AdditionalFields - это varchar (max)
База данных - это SQL Server 10 с уровнем совместимости = Sql Server 2005 (90)
Я использую Management Studio 2008

Кажется, у меня есть пустые строки, длина которых равна NULL, или значениям NULL, равным пустой строке. Это новый тип данных?!

EDIT: Новый тип данных - таким образом, называемый "Numpty"

РЕДАКТИРОВАТЬ 2 вставка данных во временную таблицу превращает Numpies в 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

РЕДАКТИРОВАТЬ 3 И я могу исправить данные, запустив обновление:

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

Итак, это заставляет меня думать, что поля должны содержать что-то, а не какую-то проблему с определением схемы. Но что это? И как мне его остановить?

РЕДАКТИРОВАТЬ 4 В моей базе данных есть еще 2 поля, оба varchar (max), которые возвращают строки, когда поле NOT NOT и LEN (поле) IS NULL. Все эти поля были когда-то TEXT и были изменены на VARCHAR (MAX). База данных также была перенесена с Sql Server 2005 на 2008. Похоже, у нас есть ANSI_PADDING и т.д. OFF по умолчанию.

Другой пример:

Преобразование в 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

Ответ 1

У меня есть образец кода для воспроизведения описанного выше поведения. Проблема возникает, когда у вас есть поле TEXT, в котором хранится значение, большее, чем оно может быть помещено в строку, и если вы установите его на NULL и выполните преобразование столбца в VARCHAR(MAX).

Большое значение сохраняется на отдельной странице. Затем вы установите для этого поля значение NULL. Если теперь вы преобразуете этот столбец в VARCHAR(MAX), то SQL Server, похоже, не будет прав. Обычно при преобразовании TEXT to VARCHAR(MAX) внешние страницы остаются такими, каковы они есть, но, возможно, потому, что он был установлен в NULL, столбец, изменяющий все вещи.

Обновление:. Похоже, что это не имеет ничего общего с большими значениями в столбце TEXT. Короткие значения показывают одинаковое поведение (расширенная выборка). Так что это просто явная настройка NULL через UPDATE и преобразование, которое имеет значение.

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

Кажется, что они помещают значения в строки, которые ранее были сохранены на внешних страницах. (См. Ссылку: NTEXT vs NVARCHAR (MAX) в SQL 2005)

Ответ 2

Это просто дополнение к McSim answer, используя средство просмотра внутренних документов SQL Server, чтобы посмотреть отдельные этапы.

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 должен следовать текстовому указателю и смотреть на его значение, чтобы определить способность NULL.

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

Это изменение только для метаданных. И данные inrow и out of row остаются неизменными.

Однако в этот момент выполняется следующее неправильное возвращение строки.

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

Вывод STATISTICS IO

Число сканирования 1, логическое чтение 2,... логическое чтение lob 1

показывает, что он по-прежнему действительно соответствует текстовому указателю, но предположительно в случае varchar(max) должен существовать другой путь кода, который некорректно заканчивается тем, что принимает значение из NULL_BITMAP независимо (значение которого никогда не обновлялось начиная с начальной вставки).

Ответ 3

Как указывали другие, этот результат совершенно невозможно.

Пожалуйста, разместите снимок экрана фактического плана выполнения.
Пожалуйста, запустите dbcc checkdb и опубликуйте сообщения об ошибках, если они есть.

(2) на самом деле мой любимый прямо сейчас.

Ответ 4

Colin:

Я уверен, что все это происходит из-за преобразования базы данных. Поскольку вам нужно решить эту проблему КАК МОЖНО СКОРЕЕ, мое предложение состоит в том, чтобы гарантировать, что ваши данные AdditionalFields в порядке, и попытайтесь понять, почему это происходит после:

Сделайте резервную копию;

Запустите этот T-SQL:

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

Функция isnull преобразует ваши нулевые значения в пустые строки, а левая/правая обрезка должна гарантировать, что даже поля с более чем одним пространством будут иметь то же значение после.

Не могли бы вы запустить это и позже сообщить нам о результатах?

С наилучшими пожеланиями

Ответ 5

Я подозреваю, что слово NULL хранится в db, используйте select * from blah, где mycolumn = 'NULL'