SQL Server : Is there a way to check what is the resulting data type of implicit conversion?

有没有办法检查隐式转换的结果数据类型是什么？或者我是否只需要根据加入算术运算的数据类型来了解它？

declare @i as int
set @i = 3
select @i  / 9.0 as returning_numeric

最佳答案

表达式的结果是 numeric (17,6) 。看到这个

DECLARE @i  INT, @v SQL_VARIANT

SET @i = 3
SET @v = @i  / 9.0

SELECT
    CAST(SQL_VARIANT_PROPERTY(@v, 'BaseType') AS VARCHAR(30)) AS BaseType,
    CAST(SQL_VARIANT_PROPERTY(@v, 'Precision') AS INT) AS Precision,
    CAST(SQL_VARIANT_PROPERTY(@v, 'Scale') AS INT) AS Scale

退货

BaseType   Precision   Scale
---------- ----------- -----------
numeric    17          6

编辑:

SELECT SQL_VARIANT_PROPERTY(9.0, 'BaseType'),
       SQL_VARIANT_PROPERTY(9.0, 'Precision'),
       SQL_VARIANT_PROPERTY(9.0, 'Scale')

所以文字 9.0 被视为 numeric(2,1) (从上面的查询可以看出)
@i 是 numeric(10,0) (根据 Mikael's answer )

控制为什么 numeric(10,0)/numeric(2,1) 给出 numeric (17,6) are covered here 的规则

Operation:        e1 / e2
Result precision: p1 - s1 + s2 + max(6, s1 + p2 + 1)
Result scale:     max(6, s1 + p2 + 1)

将相关值代入给出

10 - 0 + 1 + max(6, 0 + 2 + 1)  = 17
max(6, 0 + 2 + 1)               =  6

关于SQL Server : Is there a way to check what is the resulting data type of implicit conversion?，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5606689/