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

Рассмотрим следующий код:

SELECT TOP (10) ABS(CHECKSUM(NEWID())) % 4 FROM sys.objects;

Вышеуказанный код вернет только значения 0, 1, 2 или 3. Любое другое значение математически невозможно. Функция ABS(CHECKSUM(NEWID())) возвращает случайное положительное целое число, а оператор остатка от деления (%) возвращает остаток после деления.

Теперь давайте немного изменим код:

SELECT TOP (10) CASE ABS(CHECKSUM(NEWID())) % 4
                    WHEN 0 THEN 0
                    WHEN 1 THEN 1
                    WHEN 2 THEN 2
                    WHEN 3 THEN 3
                END
FROM sys.objects;

В этом случае вы можете ожидать такого же поведения, как и раньше, но вы можете заметить, что код может возвращать значения NULL. Это может заставить вас задаться вопросом, является ли SQL Server математически неправильным или следует избегать использования оператора остатка от деления.

Однако, когда мы рассматриваем план выполнения, мы видим, что SQL Server фактически выполняет код по-разному:

SELECT TOP (10) CASE
                    WHEN ABS(CHECKSUM(NEWID())) % (4) = 0 THEN 0
                    ELSE CASE
                            WHEN ABS(CHECKSUM(NEWID())) % (4) = 1 THEN 1
                            ELSE CASE
                                    WHEN ABS(CHECKSUM(NEWID())) % (4) = 2 THEN 2
                                    ELSE CASE
                                            WHEN ABS(CHECKSUM(NEWID())) % (4) = 3 THEN 3
                                            ELSE NULL
                                        END
                                END
                        END
                END
FROM sys.objects;

Из плана выполнения ясно, что значения NULL происходят из того факта, что функция ABS(CHECKSUM(NEWID())) выполняется несколько раз, что приводит к разным значениям каждый раз.

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

Итак, какова практическая ценность этого знания? Важно ли, если мы не можем использовать NEWID() в операторе CASE? Хотя это может не быть значительной проблемой в этом конкретном сценарии, это подчеркивает важность понимания того, как функции и операторы ведут себя в SQL Server.

Например, рассмотрим простую функцию в базе данных AdventureWorks:

CREATE FUNCTION fnGetOrderDate (@SalesOrderId INTEGER)
RETURNS DATE
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @OrderDate SMALLDATETIME;
    SELECT @OrderDate = OrderDate
    FROM sales.SalesOrderHeader
    WHERE SalesOrderID = @SalesOrderId;
    RETURN @OrderDate;
END;

Если мы используем эту функцию в операторе CASE, мы можем столкнуться с проблемами производительности:

SELECT CASE DATEPART(MONTH, dbo.fnGetOrderDate(45038))
            WHEN 1 THEN 'Jan'
            WHEN 2 THEN 'Feb'
            WHEN 3 THEN 'Mar'
            WHEN 4 THEN 'Apr'
            WHEN 5 THEN 'May'
            WHEN 6 THEN 'Jun'
            WHEN 7 THEN 'Jul'
            WHEN 8 THEN 'Aug'
            WHEN 9 THEN 'Sept'
            WHEN 10 THEN 'Oct'
            WHEN 11 THEN 'Nov'
            WHEN 12 THEN 'Dec'
        END
FROM sys.objects;

Когда мы изменяем передаваемый SalesOrderId на другое значение, например, 43659, мы можем заметить снижение производительности. Это подчеркивает потенциальное влияние использования скалярных пользовательских функций в определенных сценариях.

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