precio - ¿Error o característica de SQL Server? Conversión de números decimales

sql server decimal format

Question

Durante el desarrollo se enfrentó con un comportamiento bastante extraño de SQL Server. Aquí tenemos absolutamente la misma fórmula para absolutamente el mismo número. La única diferencia es cómo obtenemos este número (4.250). Desde tabla, tabla temporal, tabla de variables o valor codificado. El redondeo y el vaciado son absolutamente iguales en todos los casos.

-- 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];

Y los resultados son:

Answer 1

Desde docs.microsoft.com/en-us/sql/t-sql/data-types/… página de docs.microsoft.com/en-us/sql/t-sql/data-types/…

Cuando utiliza los operadores aritméticos +, -, * o% para realizar la conversión implícita o explícita de los valores constantes int, smallint, tinyint o bigint a los tipos de datos flotantes, reales, decimales o numéricos, las reglas que SQL Server Se aplica cuando calcula el tipo de datos y la precisión de los resultados de la expresión varía según si la consulta está autocontaminada o no.

Por lo tanto, expresiones similares en las consultas a veces pueden producir resultados diferentes. Cuando una consulta no es autoparameterizada, el valor constante se convierte primero en numérico, cuya precisión es lo suficientemente grande como para mantener el valor de la constante, antes de convertir al tipo de datos especificado. Por ejemplo, el valor constante 1 se convierte en numeric (1, 0) , y el valor constante 250 se convierte en numeric (3, 0) .

Cuando una consulta se parametriza automáticamente, el valor constante siempre se convierte en numeric (10, 0) antes de convertir al tipo de datos final. Cuando el operador / está involucrado, no solo la precisión del tipo de resultado puede diferir entre consultas similares, sino que el valor del resultado también puede diferir. Por ejemplo, el valor del resultado de una consulta autoparameterizada que incluye la expresión SELECT CAST (1.0 / 7 AS float) será diferente del valor del resultado de la misma consulta que no es autoparameterizado, porque los resultados de la consulta autoparameterizada se truncarán para ajustarse en el tipo de datos numeric (10, 0) .

Nota:

numeric (10, 0) es equivalente a INT .

En el ejemplo anterior, cuando tanto el dividendo como el divisor son números enteros, el tipo se trata como INT por ejemplo, INT / INT = INT

Si, por otro lado, uno de los tipos es forzado a ser un tipo NUMERIC "correcto", la expresión se trata como NUMERIC( 10, 0 ) / NUMERIC( 10, 0 ) = NUMERIC( 21, 11 ) . Consulte: Precisión, escala y longitud (Transact-SQL) para obtener una explicación de cómo se calculan los tipos de resultados.

Ejemplo:

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''

Nota: el tipo de datos NUMERIC solo tiene un número fijo de decimales (escala) para almacenar números fraccionarios. Esto se vuelve importante cuando la división produce el resultado con una parte decimal larga (infinitamente), por ejemplo 1/3, que debe truncarse para ajustarse al tipo.

Caso OPs

La diferencia en los resultados se reduce a si 12 se trata como INT / NUMERIC( 10, 0 ) o NUMERIC( 2, 0 ) ya que esto afectará directamente la precisión (número de lugares decimales) del resultado: decimal(19,16) o decimal(11,8) . He eliminado las funciones CAST y ROUND para mostrar los tipos reales utilizados en el cálculo.

Parámetros de entrada:

-- 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''

En el caso anterior se trata como INT .

Puede "forzar" para que sea tratado como 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)

Fórmula para calcular el tipo de datos del producto: p1 + p2 + 1, s1 + s2 .

Para averiguar el tipo inicial, resuelva: 5 = x + 2 + 1, 2 = y + 2 para obtener 2, 0 es decir, NUMERIC( 2, 0 )

El tipo de salida del resultado será el siguiente:

-- 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

Para ver cómo se calculan los tipos de resultados, consulte Precisión, escala y longitud (Transact-SQL) .

Solución

Ponga sus literales y / o resultados intermedios al tipo deseado para evitar sorpresas, por ejemplo

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

Resumen:

Esta pregunta es un caso complejo de: División de 2 números mediante la función CAST en el servidor SQL 2008R2 . Con la complejidad derivada del hecho de que SQL Server puede usar diferentes tipos de datos en diferentes escenarios.

Una palabra sobre parametrización simple.

Solo pude encontrar un artículo ( http://www.sqlteam.com ) sobre la parametrización simple que realmente menciona cuando / cuando no una consulta se parametrizaría automáticamente.

Nota: El artículo es de 2007 por lo que puede no estar actualizado.

SQL Server coloca las siguientes restricciones sobre qué tipos de consultas se pueden parametrizar utilizando la parametrización simple:

• Mesa única - No hay combinaciones
• No hay cláusula de entrada
• No UNION
• No SELECT INTO
• No hay sugerencias de consulta
• No DISTINCT o TOP
• Sin texto completo, servidores vinculados o variables de tabla
• No hay sub-consultas
• No GRUPO POR
• No <> en la cláusula WHERE
• Sin funciones
• No BORRAR o ACTUALIZAR con la cláusula FROM
• Los valores de los parámetros no pueden afectar el plan.

TechNet - El artículo de parametrización simple no tiene información.

off tiene cierta información, pero se aplica a la parametrización forzada

Answer 2

Esto parece deberse a que no ha especificado el tipo de datos de 4.250 en todos los lugares donde ha codificado ese valor, junto con la combinación de tipos de datos decimal(5,3) y decimal(15,9) en sus declaraciones de tabla y declaraciones de conversión.

Tenga en cuenta que especificando la misma precisión en todas partes:

-- 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];

Obtienes el mismo resultado para cada fila:

0.003541667

Nota adicional:

Puede hacer una prueba para ver en qué tipo de datos es su valor numérico codificado en una variante:

DECLARE @var SQL_VARIANT;

SELECT @var = 4.250

SELECT SQL_VARIANT_PROPERTY(@var, ''BaseType''),
       SQL_VARIANT_PROPERTY(@var, ''Precision''),
       SQL_VARIANT_PROPERTY(@var, ''Scale'');

Esto devuelve numeric(4,3) en mi caja local de SQL Server. (Numérico y decimal son la misma cosa )

Edición # 2: excavación adicional

Tomando solo el primer ejemplo:

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

Después de haber cavado un poco más, los planes de ejecución son diferentes: la primera declaración se está parametrizando, mientras que la versión de la subconsulta no es:

Si nos fijamos en la ventana de propiedades:

No enumera los tipos de datos de estos parámetros, pero al hacer el mismo truco rellenando los valores 0.01 y 12 en una variante, se obtiene numeric(2,2) tipos de datos numeric(2,2) e int respectivamente.

Si convierte los valores codificados en la segunda declaración a esos tipos de datos:

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

Obtienes el mismo resultado para ambas afirmaciones. Por qué ha decidido parametrizar la selección pero no la subconsulta, cuáles son los tipos de datos de los parámetros en realidad, y qué tipos de datos se tratan los valores codificados normalmente en la segunda declaración ... sigue siendo un misterio para mí. Probablemente tendríamos que preguntarle a alguien con conocimiento interno del motor de SQL Server.

Answer 3

Si corro:

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

Se 0.003541660 valor 0.003541660 .

Si corro:

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

Se 0.003541667 valor 0.003541667 .

Me huele mucho a un insecto ...

editar

Basándome en la respuesta de Bridge, yo también decidí echar un vistazo a los planes de ejecución. Lo y he aquí:

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)

Ambas consultas devuelven 0.003541660 . Así que parece que la reutilización del plan de ejecución es donde se origina el ''error''. (Nota: DBCC FREEPROCCACHE no tiene el mismo resultado!)

Nota adicional: si guardo los planes de ejecución como xml, los archivos son idénticos con y sin OPTION (RECOMPILE) .

editar:

Si configuro la base de datos en PARAMETERIZATION FORCED , la subconsulta aún se ejecuta sin parámetros. Si fuerzo la parametrización utilizando explícitamente 0.01 y 12 como variables, el valor devuelto vuelve a ser el mismo. Creo que SQL Server define los parámetros en un tipo de datos diferente al esperado. Sin embargo, no he podido forzar el resultado a 0.003541660. Esto también explica por qué la OPTION(RECOMPILE) produce valores idénticos: si se utiliza RECOMPILE, la parametrización se off .