Ответ 1

Это похоже на то, что вы не указали тип данных 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) и 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.

Ответ 2

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

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.

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

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

Возвращается значение 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, параметризация поворачивается off.

Ответ 3

От Типы данных 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 i.e. 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 устанавливает следующие ограничения на типы запросов можно параметризовать с помощью простой параметризации:

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

TechNet - простая параметризациястатья не имеет информации.

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