SQL Server:是否有方法检查隐式转换的结果数据类型?

Is there a way to check what is the resulting data type of implicit conversion? Or do I simply have to know it based on data types joining the arithmetic operation?

有没有办法检查隐式转换的结果数据类型是什么?还是我仅仅需要根据加入算术运算的数据类型知道它?

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

2 个解决方案

#1

The result of the expression is numeric (17,6). To see this

表达式的结果是数值的(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

Returns

返回

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

Edit:

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

So the literal 9.0 is treated as numeric(2,1) (Can be seen from the query above)

因此，文字9.0被视为数字(2,1)(从上面的查询中可以看到)

@i is numeric(10,0) (as per Mikael's answer)

@i是数值(10,0)(根据Mikael的答案)

The rules that govern why numeric(10,0)/numeric(2,1) gives numeric (17,6) are covered here

这里介绍了控制为什么数字(10,0)/数字(2,1)给出数字(17,6)的规则

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

Substituting the relevant values in gives

代入给定的相关值

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

#2

The implicit conversion done here is on @i to numeric(10,0). You can modify your statement and look at the execution plan.

这里所做的隐式转换是@i到数值(10,0)。您可以修改语句并查看执行计划。

declare @i int

set @i = 3
select @i  / 9.0
from (select 1) as x(x)

Extract from execution plan

从执行计划

<ScalarOperator ScalarString="CONVERT_IMPLICIT(numeric(10,0),[@i],0)/(9.0)">

However, the resulting data type is numeric(17,6) as Martin showed in his answer and that is not shown in the execution plan.

但是，结果的数据类型是数值的(17,6)，正如Martin在他的答案中所显示的那样，这在执行计划中没有显示。