Поделиться через

  • Статья

Устранение ошибок и предупреждений по выражениям запроса

Иногда SQL Server 2008 вычисляет выражения в запросах быстрее, чем SQL Server 2000. Это дает следующие важные преимущества:

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

  • Исключаются лишние вычисления результатов выражений.

Однако в зависимости от характера запроса и содержимого базы данных, в SQL Server 2008 содержащиеся в запросе небезопасные выражения могут привести к возникновению исключений. Эти исключения во время обработки включают в себя следующее:

  • Арифметические исключения: деление на ноль, переполнение, потеря точности.

  • Ошибки преобразований: потеря точности, попытка преобразовать нечисловую строку в число и др.

  • Статистическая обработка набора значений, не все из которых гарантированно не равны NULL.

В SQL Server 2000 такие исключения в ряде приложений, использующих определенные данные, могут не возникнуть. Однако в SQL Server 2008 план запроса, изменяющийся из-за изменения статистики, может привести к исключению. Такие исключения во время выполнения можно предотвратить, включив в запрос условные выражения — NULLIF или CASE.

Важное примечаниеВажно!

Выражения, которые встречаются в условии поиска, списке выборки или в любом другом месте запроса, можно переупорядочить или разбить на несколько независимых выражений. В SQL Server существует возможность их вычисления в произвольном порядке. Операции фильтрации (включая соединения) не обязательно применяются до вычисления столбцов результатов.

В следующем примере выражение x/y в списке выборки может быть вычислено в любой момент, даже для тех строк, которые не попадают в результат запроса.

USE tempdb
GO
IF OBJECT_ID('T','U') IS NOT NULL
    DROP TABLE T
IF OBJECT_ID('S','U') IS NOT NULL
    DROP TABLE S
GO
CREATE TABLE T(x float, y float, z nvarchar(30))
CREATE TABLE S(a float, b float)
GO
INSERT INTO T VALUES (1, 0, 'unknown')
INSERT INTO T VALUES(1, 2, '10')
GO
INSERT INTO S VALUES (1, 1)
INSERT INTO S VALUES (1, 2)
INSERT INTO S VALUES (1, 3)
INSERT INTO S VALUES (1, 4)
INSERT INTO S VALUES (1, 5)

Выполнение в SQL Server 2008 следующего запроса приведет к ошибке, тогда как в SQL Server 2000 оно будет выполнено.

SELECT x/y FROM T INNER JOIN S ON x = a AND y > b
OPTION(HASH JOIN)

Выражение x/y, если y=0, вызовет ошибку деления на ноль.

Следующий фрагмент кода представляет собой решение, позволяющее правильно выполнить запрос:

SELECT x/NULLIF(y,0) FROM T INNER JOIN S ON x = a AND y > b
OPTION(HASH JOIN)

Выражение NULLIF(y,0) возвращает NULL, если y = 0. В противном случае выражение возвращает значение для y. Выражение x/NULL приводит к результату NULL и исключения не возникают.

Рассмотрите следующий пример, показывающий преобразование символьных данных в числовые типы.

SELECT CONVERT(tinyint, z) FROM T INNER JOIN S ON x = a AND y > b
OPTION(HASH JOIN)

Запрос приводит к ошибке при попытке преобразовать строку 'unknown' в тип tinyint. Одним из способов решения этой проблемы является изменение запроса таким образом, чтобы он выполнял преобразование значения только в том случае, если z имеет тип numeric. Для этого нужно добавить инструкцию CASE следующим образом:

SELECT CASE WHEN ISNUMERIC(z) = 1
    THEN CONVERT(tinyint, z) 
    ELSE 0 
END
FROM T INNER JOIN S ON x = a AND y > b
OPTION(HASH JOIN)

Другим решением является отказ от использования в базе данных специального символьного значения 'unknown' и использовать вместо него значение NULL. Третье решение — сменить тип столбца z на tinyint, в таком случае можно будет вообще обойтись без преобразований. Поскольку подобного рода решения требуют изменения данных и схем, то они, соответственно, более трудоемки, чем простое изменение запроса. Тем не менее, если они позволят облегчить написание и других запросов, следует рассмотреть их целесообразность.

Предупреждение «NULL во входных значениях», выдаваемое статистическими функциями

Статистические функции, вроде MIN, если во входных параметрах содержится значение NULL, выдают предупреждение об исключении значения NULL. Это предупреждение может зависеть от плана. Если обработка входных значений NULL для статистических выражений не нужна, а также чтобы избежать выдачи предупреждений, можно локально изменить запрос, исключив эти значения. Рассмотрим инструкцию SELECT в следующем примере:

USE tempdb
GO
IF OBJECT_ID('newtitles','U') IS NOT NULL
....DROP TABLE newtitles 
GO
CREATE TABLE dbo.newtitles 
   (title varchar (80) NULL ,
    pubdate datetime NULL)
GO
INSERT dbo.newtitles VALUES('Title 1', NULL)
INSERT dbo.newtitles VALUES('Title 2', '20050311')
GO
SELECT t.title, t.pubdate, m.min_pubdate
FROM newtitles AS t,
   (SELECT MIN(pubdate) AS min_pubdate 
    FROM newtitles) AS m
WHERE t.pubdate = m.min_pubdate
GO

В SQL Server 2008 этот запрос приводит к выдаче предупреждения. Чтобы избежать этого, добавьте в запрос условие WHERE pubdate IS NOT NULL, чтобы отфильтровать значения NULL, прежде чем вычислять статистическое выражение:

SELECT t.title, t.pubdate, m.min_pubdate
FROM newtitles AS t,
   (SELECT MIN(pubdate) AS min_pubdate 
    FROM newtitles
    WHERE pubdate IS NOT NULL) AS m
WHERE t.pubdate = m.min_pubdate
GO

См. также

Другие ресурсы

Диагностика запросов