Bug ou fonctionnalité du serveur SQL ? Conversion des nombres décimaux

Question

Au cours du développement, j'ai été confronté à un comportement assez étrange du serveur SQL. Ici, nous avons absolument la même formule pour absolument le même nombre. La seule différence est la façon dont nous obtenons ce nombre (4.250). Depuis une table, une table temporaire, une table variable ou une valeur codée en dur. L'arrondi et le moulage sont absolument les mêmes dans tous les cas.

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

Et les résultats sont :

Answer 1

Cela semble être dû au fait que vous n'avez pas spécifié le type de données de 4.250 partout où vous avez codé en dur cette valeur, ainsi que le mélange de types de données. decimal(5,3) y decimal(15,9) dans vos déclarations de tables et vos instructions de cast.

Notez qu'en spécifiant la même précision partout :

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

Vous obtenez le même résultat pour chaque ligne :

0.003541667

Note supplémentaire :

Vous pouvez tester le type de données de votre valeur numérique codée en dur en la plaçant dans une 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');

Ce retour numeric(4,3) sur ma boîte locale de SQL Server. (Numérique et Décimal sont les même chose )

Edit #2 : En creusant davantage

Prenons simplement le premier exemple :

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

Après avoir creusé un peu plus, les plans d'exécution sont différents - la première déclaration est paramétrée, alors que la version sous-requête ne l'est pas :

Si vous regardez la fenêtre des propriétés :

Il n'y a pas de liste des types de données de ces paramètres, mais en faisant le même tour de passe-passe avec le remplissage des valeurs 0.01 y 12 dans une variante se retrouve avec des types de données numeric(2,2) y int respectivement.

Si vous convertissez les valeurs codées en dur dans la deuxième déclaration en ces types de données :

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

Vous obtenez le même résultat pour les deux déclarations. La raison pour laquelle il a été décidé de paramétrer le select mais pas la sous-requête, quels sont les types de données des paramètres et quels types de données les valeurs codées en dur sont traitées normalement dans la seconde instruction... restent un mystère pour moi. Il faudrait probablement demander à quelqu'un ayant une connaissance interne du moteur SQL Server.

Answer 2

Si je me présente :

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

Valeur 0.003541660 est renvoyé.

Si je me présente :

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

Valeur 0.003541667 est renvoyé.

Ça sent très fort comme un insecte pour moi...

modifier

Sur la base de la réponse de Bridge, j'ai décidé moi aussi de jeter un coup d'œil aux plans d'exécution. Et voilà :

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)

Les deux requêtes retournent 0.003541660 . Il semble donc que la réutilisation du plan d'exécution soit à l'origine de l'"erreur". (Note : DBCC FREEPROCCACHE n'a pas le même résultat)

Note supplémentaire : Si j'enregistre les plans d'exécution au format xml, les fichiers sont identiques avec et sans OPTION (RECOMPILE) .

éditer :

Si je règle la base de données sur PARAMETERIZATION FORCED la sous-requête est toujours exécutée sans paramètres. Si je force le paramétrage en utilisant explicitement 0.01 y 12 comme variables, la valeur renvoyée est à nouveau la même. Je pense que SQL Server définit les paramètres dans un type de données différent de celui attendu. Je n'ai pas été capable de forcer le résultat à 0.003541660 cependant. Cela explique aussi pourquoi OPTION(RECOMPILE) donne des valeurs identiques : Si RECOMPILE est utilisé, le paramétrage est désactivé. off .

Answer 3

Desde Types de données SQL Server page

Lorsque vous utilisez les opérateurs arithmétiques +, -, *, /, ou % pour exécuter conversion implicite ou explicite de valeurs constantes de type int, smallint, tinyint ou bigint vers les types de données float, real, decimal ou numeric, les règles que le serveur règles que SQL Server applique lorsqu'il calcule le type de données et la précision des de données et la précision des résultats de l'expression diffèrent selon que la requête est autoparamétrée ou non.

Par conséquent, des expressions similaires dans les requêtes peuvent parfois produire des résultats différents. Lorsqu'une requête n'est pas autoparamétrisée, la valeur de la constante est tout d'abord convertie en numérique, dont la précision est juste assez grande pour contenir la valeur de la constante, avant d'être convertie vers le type de données type de données spécifié. Par exemple, la valeur constante 1 est convertie en numeric (1, 0) et la valeur constante 250 est convertie en numeric (3, 0) .

Quand une requête est autoparamétrisée, la valeur constante est toujours convertie en numeric (10, 0) avant de convertir en données finales final. Lorsque l'opérateur / est impliqué, non seulement la précision du type de résultat peut varier d'une requête à l'autre, mais la valeur du résultat peut également varier. du type de résultat peut varier entre des requêtes similaires, mais la valeur du résultat peut également varier. différer également. Par exemple, la valeur du résultat d'une requête autoparamétrisée qui inclut l'expression SELECT CAST (1.0 / 7 AS float) sera différent de la valeur du résultat de la même requête qui n'est pas autoparamétrée, parce que les résultats de la requête autoparamétrée sont seront tronqués pour tenir dans le champ numeric (10, 0) type de données.

Nota:

numeric (10, 0) est équivalent à INT .

Dans l'exemple ci-dessus, lorsque le dividende et le diviseur sont tous deux des nombres entiers, le type est traité comme suit INT par exemple INT / INT = INT

Si, d'un autre côté, l'un des types est forcé d'être un "vrai". NUMERIC l'expression est traitée comme NUMERIC( 10, 0 ) / NUMERIC( 10, 0 ) = NUMERIC( 21, 11 ) . Voir : Précision, échelle et longueur (Transact-SQL) pour une explication de la façon dont les types de résultats sont calculés.

Exemple :

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: NUMERIC ne dispose que d'un nombre fixe de décimales (échelle) pour stocker les nombres fractionnaires. Cela devient important lorsque la division produit un résultat avec une partie décimale (infiniment) longue, par exemple 1 / 3, qui doit être tronquée pour s'adapter au type.

Cas du PO

La différence de résultats se résume à savoir si 12 est traité comme INT / NUMERIC( 10, 0 ) o NUMERIC( 2, 0 ) car cela affectera directement la précision (nombre de décimales) du résultat : decimal(19,16) o decimal(11,8) . J'ai enlevé CAST y ROUND pour montrer les types réels utilisés dans le calcul.

Paramètres d'entrée :

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

Dans le cas ci-dessus, il est traité comme INT .

Vous pouvez le "forcer" à être traité en tant que 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)

Formule de calcul du type de données produit : p1 + p2 + 1, s1 + s2 .

Pour trouver le type de départ, résolvez : 5 = x + 2 + 1, 2 = y + 2 pour obtenir 2, 0 c'est-à-dire NUMERIC( 2, 0 )

Le type de sortie du résultat sera le suivant :

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

Pour voir comment les types de résultats sont calculés, voir Précision, échelle et longueur (Transact-SQL) .

Solution

Mettez vos littéraux et / ou vos résultats intermédiaires dans le type souhaité pour éviter les surprises, par exemple

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

Résumé :

Cette question est un cas complexe de : Division de 2 nombres à l'aide de la fonction CAST dans SQL server 2008R2 . La complexité provient du fait que SQL Server peut utiliser différents types de données dans différents scénarios.

Un mot sur le paramétrage simple

Je n'ai pu trouver qu'un seul article ( http://www.sqlteam.com ) sur le paramétrage simple qui mentionne quand/quand une requête doit être paramétrée automatiquement.

Nota: L'article date de 2007 et peut donc ne pas être d'actualité.

Le serveur SQL impose les restrictions suivantes sur les types de requêtes suivantes peuvent être paramétrés à l'aide de Simple Parameterization :

• Tableau unique - Pas de JOINs
• Pas de clause IN
• Non UNION
• Non SELECT INTO
• Pas de conseils pour les requêtes
• Pas de DISTINCT ou de TOP
• Pas de texte intégral, de serveurs liés ou de variables de table.
• Pas de sous-requêtes
• Non GROUP BY
• Pas de <> dans la clause WHERE
• Aucune fonction
• Pas de DELETE ou UPDATE avec clause FROM
• Les valeurs des paramètres ne peuvent pas affecter le plan

TechNet - Paramétrage simple L'article ne contient aucune information.

TechNet - Paramétrage forcé contient des informations, mais elles s'appliquent au paramétrage forcé.