SQL-Ex blog

Скрытый "дефект" в STR()

Множество людей никогда не углубляются достаточно в Books Online, чтобы осознать, что используемое ими может заложить большой, но скрытый "дефект" в основании их кода. Если вы действительно хорошо ознакомились с STR() в Books Online, то можете что-то заметить, но все же не понять отклонений. Посмотрите внимательно. Видите? Видите скрытый "дефект"?

Синтаксис

STR ( float_expression [ , length [ , decimal ] ] )

Все еще не видите? Давайте я вам покажу...

Синтаксис

STR ( float_expression [ , length [ , decimal ] ] )

"float_expression" (выражение типа float). Это означает, что любое числовое значение, которое вы пытаетесь отформатировать с помощью STR(), сначала неявно конвертируется к типу данных FLOAT. Что это означает для вас? Хех... это означает огромные "дефекты" в вашем коде, если вы не соблюдаете осторожность. Давайте посмотрим, что неявное преобразование к FLOAT делает с определенными значениями при форматировании с помощью STR().

Ограниченное число цифр

Выполните следующий код. Значение в самом первом SELECT представляет собой максимальное значение для BIGINT. Значение в самом последнем SELECT фактически все еще больше, чем INTEGER. Что будет возвращено?

SELECT 9223372036854775807, STR(9223372036854775807,19) UNION ALL

 SELECT 922337203685477580,  STR(922337203685477580,19)  UNION ALL

 SELECT 92233720368547758,   STR(92233720368547758,19)   UNION ALL

 SELECT 9223372036854775,    STR(9223372036854775,19)    UNION ALL

 SELECT 922337203685477,     STR(922337203685477,19)

Вот вывод:

9223372036854775807    9223372036854775800

922337203685477580          922337203685477630

92233720368547758           92233720368547760

9223372036854775            9223372036854776

922337203685477             922337203685477

Если посмотреть внимательно, то, помимо правильного числа цифр, преобразование STR() возвращает неверные значения в правом столбце для всех строк, за исключением последней. Последняя строка, которая единственная была преобразована верно, имеет 15 цифр, что является также ограничением для типа данных FLOAT. Это потому, что STR () сначала преобразует значение в FLOAT.

То же самое имеет место справа от десятичной точки. Выполните следующий код, и скажите, что вы видите:

SELECT .9223372036854775807, STR(.9223372036854775807,19,19) UNION ALL

 SELECT .922337203685477580,  STR(.922337203685477580,19,19)  UNION ALL

 SELECT .92233720368547758,   STR(.92233720368547758,19,19)   UNION ALL

 SELECT .9223372036854775,    STR(.9223372036854775,19,19)    UNION ALL

 SELECT .922337203685477,     STR(.922337203685477,19,19)

Вот результат:

0.9223372036854775807          0.9223372036854776

0.9223372036854775800          0.9223372036854777

0.9223372036854775800          0.9223372036854776

0.9223372036854775000          0.9223372036854776

0.9223372036854770000          0.9223372036854770

Теперь мы видим 16 значащих цифр, но преобразование STR() снова возвращает неверные значения для правого столбца, и, наконец, нам повезло, когда мы набрали всего 15 значащих цифр. Я говорю "повезло", поскольку это еще не конец истории.

Неверное округление

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

--===== Типичные проблемы округления такие же, как у FLOAT

WITH cteGenerateExampleData AS

( --=== Строим миллион строк, которые начинаются с 0.001 и заканчиваются на 1000.000

     -- with an increment of 0.001. This only takes a couple of seconds. 

 SELECT TOP 1000000

        SomeNumber = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*0.001

   FROM sys.all_columns ac1

  CROSS JOIN sys.all_columns ac2

)

, cteConversions AS

( --=== Давайте округлим число, используя ROUND, преобразование к DECIMAL и STR()

 SELECT SomeNumber,

        SomeRound   = ROUND(SomeNumber,2),

        SomeDecimal = CAST(SomeNumber AS DECIMAL(10,2)),

        SomeSTR     = STR(SomeNumber,10,2)

   FROM cteGenerateExampleData

) --=== Наконец покажем, где числа, отформатированные Decimal и STR()

     -- отличаются от правильно округленного значения.

 SELECT SomeNumber,

        SomeRound,

        SomeDecimal,

        SomeSTR,

        ErrorReason = ISNULL(CASE WHEN SomeDecimal <> SomeRound THEN 'Bad Decimal Rounding ' END,'')

                    + ISNULL(CASE WHEN SomeStr     <> SomeRound THEN 'Bad STR Rounding '     END,'')

   FROM cteConversions

  WHERE SomeDecimal <> SomeRound

     OR SomeStr     <> SomeRound

;

Я не хочу выводить здесь все 404507 строк, но вот первые 10:



Как вы можете увидеть, число, отформатированное с помощью STR(), не всегда округляет правильно. Это происходит потому, что происходящее преобразование к FLOAT для чисел типа 5.145 фактически дает 5.144999999999999, и это причина неверного округления от третьего десятичного знака. STR() видит число 5.144 и округляет его к 5.14 вместо 5.145.

Если вам действительно нужен простой способ, при котором STR() дает правильные результаты, вот пара правил, которым вы должны следовать:

1. Никогда не используйте более 15 цифр.


2. Не используйте STR() для округления. Выполните правильное округление с помощью ROUND или соответствующего преобразования к DECIMAL. Затем используйте STR(), чтобы сделать правильное выравнивание.

Теперь ... прежде чем радоваться использованию STR () только потому, что вы знаете правила, мы продолжим ...

STR() медленная!

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

--===== Удаление и новое построение тестовой таблицы 

     -- в желаемом месте. Это займет только несколько секунд.

     IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

        DROP TABLE #MyHead

;

 SELECT TOP 1000000

        SomeNumber = IDENTITY(INT,1,1)

   INTO #MyHead

   FROM sys.all_columns ac1

  CROSS JOIN sys.all_columns ac2

;

--===== Объявляем переменную, которая используется для отображения времени

DECLARE @BitBucket NVARCHAR(10)

;

--===== Определяем тест и включаем таймер для отображения

  PRINT '========== Cast, Concatenate, and Size ==========';

    SET STATISTICS TIME ON

;

--===== Немного более сложный метод для правильного выравнивания, 

--который более чем в два раза быстрей форматирования с помощью STR()

 SELECT @BitBucket = RIGHT('0000000000' + CAST(SomeNumber AS VARCHAR(10)),10)

   FROM #MyHead

;

    SET STATISTICS TIME OFF

;

--===== Определяем тест и включаем таймер для отображения

  PRINT '========== The STR() Method is SLOWER ==========';

    SET STATISTICS TIME ON

;

 SELECT @BitBucket = STR(SomeNumber,10)

   FROM #MyHead

;

--===== Выключаем "часы"

    SET STATISTICS TIME OFF

;

Вот результаты вычислений. Конечно, ваши времена, вероятно, будут в целом существенно быстрей, чем я получил на моем восьмилетнем ПК.