Подробнее о неявных преобразованиях

Автор: Craig Freedman More on Implicit Conversions

Меня попросили прокомментировать алгоритм, который SQL Server использует для выбора неявных преобразований. Когда SQL Server сталкивается в выражении с несовпадающими типами, он может выполнить запрос с неявным преобразованием или может завершить запрос ошибкой. Прежде чем углубляться в сценарий неявного преобразования, давайте кратко рассмотрим случай с ошибкой. Если неявное преобразование невозможно, возможны два варианта ошибки. Если оба типа данных несовместимы (то есть, если между двумя типами не допускается преобразование, неявное или явное), SQL Server сгенерирует следующую ошибку:

DECLARE @a INT
DECLARE @b DATE
SET @a = @b

Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int

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

DECLARE @a INT
DECLARE @b DATE
SET @a = CONVERT(INT,@b)

Msg 529, Level 16, State 2, Line 3
Explicit conversion from data type date to int is not allowed.

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

DECLARE @a INT
DECLARE @b DATETIME
SET @a = @b

Msg 257, Level 16, State 3, Line 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Добавление явного преобразования решает проблему:

DECLARE @a INT
DECLARE @b DATETIME
SET @a = CONVERT(INT,@b)

В документе Books Online есть матрица совместимости всех возможных комбинаций типов данных в SQL Server.

Если SQL Server решает выполнить для выражения неявное преобразование, он должен выбрать, какую сторону выражения преобразовывать, и какой там должен быть тип для преобразования. В принципе, тут есть три варианта:

1. Преобразуйте правую часть к типу в левой части.
2. Преобразуйте левую часть к типу в правой части.
3. Преобразуйте обе стороны к третьему, другому типу.

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

DECLARE @a INT
DECLARE @b DATETIME
SET CONVERT(DATETIME,@a) = @b

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'convert'.

На практике SQL Server выбирает один из двух первых вариантов. В частности, SQL Server ранжирует все типы и преобразует типы с меньшим рангом к типу с более высоким рангом. Например, поскольку ранг INT ниже, чем у DATETIME, запрос ниже преобразует переменную INT к DATETIME:

DECLARE @a INT
DECLARE @b DATETIME
SELECT 0 WHERE @a = @b

  |--Compute Scalar(DEFINE:([Expr1000]=(0)))
       |--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@a],0) =[@b])))
            |--Constant Scan

Такой подход имеет несколько недостатков. В частности, хотя обычно преобразования выполняются без потерь, некоторые будут с потерями, а некоторые преобразования будут вообще невозможны. Например, преобразование SMALLINT в INT выполняется без потерь. Набор значений, которые могут быть выражены с помощью INT, является надмножеством набора значений, которые могут быть выражены с помощью SMALLINT. Преобразование INT в REAL может выполняться с потерями, а большие значения INT невозможно преобразовать к REAL:

DECLARE @a INT
DECLARE @b REAL
DECLARE @c INT
SET @a = 1000000001
SET @b = CONVERT(REAL,@a)
SET @c = CONVERT(INT,@b)
SELECT @a, @b, @c

 

a                    b             c

-------------------- ------------- --------------------

1000000001           1E+09         1000000000

Преобразование REAL в INT может привести к потерям (INT не имеет дробной части) и может завершиться ошибкой:

DECLARE @a REAL
DECLARE @b INT
SET @a = 1e13
SET @b = CONVERT(INT,@a)

Msg 232, Level 16, State 3, Line 4
Arithmetic overflow error for type int, value = 9999999827968.000000.

Используемое SQL Server ранжирование типов это попытка минимизировать риск потерь или неудачных преобразований, но иногда потеря данных или сбои неизбежны, как в случае REAL и INT.
Особенно проблематичны преобразования между строковыми типами (например, CHAR или VARCHAR) и числовыми типами (например, INT, REAL или DECIMAL). SQL Server ранжирует строковые типы ниже числовых типов и всегда будет преобразовывать из строкового типа в числовой тип. Это преобразование часто завершиться неудачей. Очевидно, что строки могут содержать нечисловые данные, и их невозможно преобразовать к числу. Однако преобразование в обратном направлении ещё более проблематично. Существует много способов представления числа в виде строки. Все следующие строки представляют одно и то же число: '1', '˽1', '1˽ ', '+1', '1.0' и '01'. Хотя SQL Server может выбрать канонический формат для преобразования чисел в строку, без преобразования строки в число сложно сравнивать строку с числовым значением, которая еще не находится в каноническом формате. Например, рассмотрим результаты следующих сравнений:

DECLARE @a INT
DECLARE @b CHAR(4)
SET @a = 1
SET @b = @a
SELECT @a AS a, @b AS b,
    CASE WHEN @a = '1'  THEN 'True' ELSE 'False' END AS [a = '1'],
    CASE WHEN @a = '+1' THEN 'True' ELSE 'False' END AS [a = '+1'],
    CASE WHEN @b = '1'  THEN 'True' ELSE 'False' END AS [b = '1'],
    CASE WHEN @b = '+1' THEN 'True' ELSE 'False' END AS [b = '+1']

 

a           b    a = '1' a = '+1' b = '1' b = '+1'

----------- ---- ------- -------- ------- --------

1           1    True    True     True    False

Обратите внимание, что при сравнении целых чисел 1 = +1, но при сравнении их как строк '1' <> '+1'.

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