Ошибка или функция SQL Server? Преобразование десятичных чисел

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

-- normal table CREATE TABLE [dbo].[value] ( [val] [decimal] (5, 3) NOT NULL ) INSERT INTO [value] VALUES (4.250 ) SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr -- inline query from normal table SELECT * FROM (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a -- record without table SELECT ROUND(CAST(4.250 * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val -- table variable DECLARE @value AS TABLE ( val [decimal] (5, 3) ); INSERT INTO @value VALUES (4.250 ) SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM @value -- temp table CREATE TABLE #value ( val [decimal] (5, 3) ) INSERT INTO #value VALUES (4.250 ) SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr -- all records together SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr UNION ALL SELECT ROUND(CAST(4.250 * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val UNION ALL SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM @value UNION ALL SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr DROP TABLE #value; DROP TABLE [dbo].[value]; 

И результаты:

введите описание изображения здесь

Solutions Collecting From Web of "Ошибка или функция SQL Server? Преобразование десятичных чисел"

Это, по-видимому, связано с тем, что вы не указали тип данных 4.250 везде, где вы строго кодировали это значение, а также смешивали типы данных decimal(5,3) и decimal(15,9) в объявлениях таблиц и операторах высказывания.

Обратите внимание, что везде указывается одна и та же точность:

 -- normal table CREATE TABLE [dbo].[value] ( [val] DECIMAL(15, 9) NOT NULL ) INSERT INTO [value] SELECT CAST(4.250 AS DECIMAL(15, 9)) SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr -- inline query from normal table SELECT * FROM (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a -- record without table SELECT ROUND(CAST(CAST(4.250 AS DECIMAL(15, 9)) * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val -- table variable DECLARE @value AS TABLE ( val [DECIMAL] (15, 9) ); INSERT INTO @value SELECT CAST(4.250 AS DECIMAL(15, 9)) SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM @value -- temp table CREATE TABLE #value ( val [DECIMAL] (15, 9) ) INSERT INTO #value SELECT CAST(4.250 AS DECIMAL(15, 9)) SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr -- all records together SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr UNION ALL SELECT ROUND(CAST(CAST(4.250 AS DECIMAL(15, 9)) * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val UNION ALL SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM @value UNION ALL SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr DROP TABLE #value; DROP TABLE [dbo].[value]; 

Вы получаете одинаковый результат для каждой строки:

0,003541667

Дальнейшее примечание:

Вы можете проверить, какой тип данных имеет ваше твердое числовое значение, набив его в один вариант:

 DECLARE @var SQL_VARIANT; SELECT @var = 4.250 SELECT SQL_VARIANT_PROPERTY(@var, 'BaseType'), SQL_VARIANT_PROPERTY(@var, 'Precision'), SQL_VARIANT_PROPERTY(@var, 'Scale'); 

Это возвращает numeric(4,3) в моем локальном поле SQL Server. (Числовое и десятичное – одно и то же )

Редактировать # 2: Дальнейшее копание

Взяв только первый пример:

 CREATE TABLE [dbo].[value] ( [val] [decimal] (5, 3) NOT NULL ) INSERT INTO [value] VALUES (4.250 ) SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr -- inline query from normal table SELECT * FROM (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a DROP TABLE VALUE 

Выкапывая немного дальше, планы выполнения различны – первый параметр параметризуется, тогда как версия подзапроса не является:

планы выполнения

Если вы посмотрите на окно свойств:

введите описание изображения здесь

Он не перечисляет типы данных этих параметров, но выполнение того же трюка с наложением значений 0.01 и 12 в вариант заканчивается с numeric(2,2) типами данных numeric(2,2) и int соответственно.

Если вы передадите жестко заданные значения во втором выражении этим типам данных:

 SELECT * FROM (SELECT ROUND(CAST(val * CAST(0.01 AS NUMERIC(2,2)) / CAST(12 AS INT) AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a 

Вы получите тот же результат для обоих операторов. Почему он решил параметризировать select, но не подзапрос, каковы типы данных параметров на самом деле и какие типы данных жестко закодированные значения рассматриваются как обычно во втором утверждении … остаются для меня загадкой. Вероятно, нам нужно будет спросить кого-нибудь, у кого есть знание SQL Server.

Если я запустил:

 SELECT CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val , SQL_VARIANT_PROPERTY(CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)), 'BaseType') FROM [value] AS pr 

0.003541660 значение 0.003541660 .

Если я запустил:

 SELECT CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val FROM [value] AS pr 

0.003541667 значение 0.003541667 .

Запах очень похож на меня …

редактировать

Основываясь на ответе Моста, я тоже решил взглянуть на планы исполнения. И вот:

 SELECT CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val FROM [value] AS pr OPTION (RECOMPILE) -- inline query from normal table SELECT a.val FROM ( SELECT CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val FROM [value] AS pr ) AS a OPTION (RECOMPILE) 

Оба запроса возвращаются 0.003541660 . Таким образом, похоже, что повторное использование плана выполнения – это ошибка «ошибки». (Примечание: DBCC FREEPROCCACHE не имеет такого же результата!)

Дополнительное примечание. Если я сохраню планы выполнения в виде xml, файлы будут идентичны как с OPTION (RECOMPILE) так и без него.

редактировать:

Если я устанавливаю базу данных в PARAMETERIZATION FORCED , подзапрос по-прежнему выполняется без параметров. Если я принудительно параметризую, используя явно 0.01 и 12 как переменные, возвращаемое значение снова будет таким же. Я думаю, что SQL Server определяет параметры в другом типе данных, чем ожидалось. Однако мне не удалось заставить результат 0.003541660. Это также объясняет, почему OPTION(RECOMPILE) приводит к идентичным значениям: Если используется RECOMPILE, параметризация отключена .

На странице типов данных SQL Server

Когда вы используете операторы +, -, *, / или% арифметические для выполнения неявного или явного преобразования константных значений int, smallint, tinyint или bigint в типы с плавающей, реальной, десятичной или числовой информацией, правила, которые SQL Server применяется, когда он вычисляет тип данных, и точность результатов выражения различается в зависимости от того, является ли запрос автопараметризированным или нет.

Поэтому подобные выражения в запросах иногда могут давать разные результаты. Когда запрос не автопараметризирован, значение константы сначала преобразуется в числовое значение, точность которого достаточно велика, чтобы удерживать значение константы перед преобразованием в указанный тип данных. Например, постоянное значение 1 преобразуется в numeric (1, 0) , а значение 250 константы преобразуется в numeric (3, 0) .

Когда запрос автоароматизирован, значение константы всегда преобразуется в numeric (10, 0) перед преобразованием в конечный тип данных. Когда задействуется оператор /, не только точность результата может отличаться среди похожих запросов, но и значение результата может также отличаться. Например, результат результата автопараметризированного запроса, который включает выражение SELECT CAST (1.0 / 7 AS float) будет отличаться от результата результата того же запроса, который не автопараметризирован, так как результаты автопараметрированного запроса будут усечены, чтобы соответствовать в numeric (10, 0) тип данных.

Заметка:

numeric (10, 0) эквивалентно INT .

В приведенном выше примере, когда оба дивиденда и делителя являются целыми числами, тип рассматривается как INT например INT / INT = INT

Если, с другой стороны, один из типов вынужден быть «правильным» NUMERIC типом, выражение рассматривается как NUMERIC( 10, 0 ) / NUMERIC( 10, 0 ) = NUMERIC( 21, 11 ) . См. « Точность, масштаб и длина» (Transact-SQL) для объяснения того, как рассчитываются типы результатов.

Пример:

 EXEC sp_describe_first_result_set N'SELECT 1 as a, 7 as b, 1 / 7 AS Result' EXEC sp_describe_first_result_set N'SELECT 1 as a, CONVERT( NUMERIC( 10, 0 ), 7 ) as b, CONVERT( INT, 1 ) / CONVERT( NUMERIC( 10, 0 ), 7 ) AS a' 

Примечание. У NUMERIC данных только фиксированное число десятичных знаков (шкала) для хранения дробных чисел. Это становится важным, когда деление производит результат с (бесконечно) длинной десятичной частью, например 1/3, которая должна быть усечена, чтобы соответствовать типу.

Случай OPs

Разница в результатах сводится к тому, будет ли 12 рассматриваться как INT / NUMERIC( 10, 0 ) или NUMERIC( 2, 0 ) как это напрямую повлияет на точность (число десятичных знаков) результата: decimal(19,16) или decimal(11,8) . Я удалил функции CAST и ROUND чтобы показать фактические типы, используемые в расчете.

Входные параметры:

 -- Note: on my machine "parameterization" option does not have any effect on below example SELECT CONVERT( decimal (5, 3), 4.250 ) AS a, -- the type is explicitly defined in the table 0.01 AS b -- always becomes NUMERIC( 2, 2 ) 12 AS c -- will either become NUMERIC( 2, 0 ) or NUMERIC( 10, 0 ) / INT EXEC sp_describe_first_result_set N'SELECT CONVERT( decimal (5, 3), 4.250 ) AS a, 0.01 AS b, 12 AS c' 

В приведенном выше случае он рассматривается как INT .

Вы можете «заставить» его обрабатывать как NUMERIC( 2, 0 ) :

 -- Note: on my machine "parameterization" option does not have any effect on below example SELECT 0.01 AS b, ( 12 * 0.01 ) AS c EXEC sp_describe_first_result_set N'SELECT ( 12 * 0.01 ) AS c' -- Result: 0.12 numeric(5,2) 

Формула для расчета типа данных продукта: p1 + p2 + 1, s1 + s2 .

Чтобы выяснить тип запуска, решите: 5 = x + 2 + 1, 2 = y + 2 чтобы получить 2, 0 т. NUMERIC( 2, 0 )

Тип вывода результата будет следующим:

 -- 12 is NUMERIC( 10, 0 ) / INT SELECT CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 ) / CONVERT( decimal(10, 0), 12 ) EXEC sp_describe_first_result_set N'SELECT CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 ) / CONVERT( decimal(10, 0), 12 )' -- Result: 0.0035416666666666 decimal(19,16) -> rounding to 9 decimal places: 0.003541667 -- 12 is NUMERIC( 2, 0 ) SELECT CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 ) / CONVERT( decimal(2, 0), 12 ) EXEC sp_describe_first_result_set N'SELECT CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 ) / CONVERT( decimal(2, 0), 12 )' -- Result: 0.00354166 decimal(11,8) -> rounding to 9 decimal places: 0.003541660 

Чтобы узнать, как рассчитываются типы результатов, см. Точность, масштаб и длина (Transact-SQL) .

Решение

Направляйте свои литералы и / или промежуточные результаты на нужный тип, чтобы избежать сюрпризов, например

 SELECT CONVERT( decimal( 12, 7 ), CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 )) / CONVERT( decimal(2, 0), 12 ) EXEC sp_describe_first_result_set N'SELECT CONVERT( decimal( 12, 7 ), CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 )) / CONVERT( decimal(2, 0), 12 )' -- Result: 0.0035416666 decimal(15,10) -> rounding to 9 decimal places: 0.003541660 

Резюме:

Этот вопрос является сложным случаем: Разделение 2 чисел с использованием функции CAST в SQL Server 2008R2 . Сложность связана с тем, что SQL Server может использовать разные типы данных в разных сценариях.

Слово о простой параметризации

Я могу найти только одну статью ( http://www.sqlteam.com ) о простой параметризации, которая фактически упоминается, когда / когда запрос не будет автоматически параметризован.

Примечание . Статья с 2007 года, возможно, не актуальна.

SQL Server устанавливает следующие ограничения на то, какие типы запросов можно параметризовать, используя Simple Parameterization:

  • Отдельная таблица – нет JOINs
  • Нет пункта IN
  • Нет СОЮЗА
  • Нет SELECT INTO
  • Нет подсказок
  • Нет DISTINCT или TOP
  • Нет полнотекстовых, связанных серверов или переменных таблицы
  • Нет подзапросов
  • Нет GROUP BY
  • Нет <> в предложении WHERE
  • Нет функций
  • Нет DELETE или UPDATE с предложением FROM
  • Значения параметров не могут повлиять на план

TechNet – статья с простой параметризацией не содержит информации.

TechNet – Forced Parameterization имеет некоторую информацию, но применяется к принудительной параметризации