|
昨天，一个读者向我提交了一个问题，请我就SQL server 隐式转换发表一些看法。当SQL server遇到一个不匹配类型的表达式的时候，它有两种选择。它使用隐式转换并能够执行或者转换错误而导致执行失败。在深入隐式转换之前，让我们假定错误的情形。
如果一个隐式转换不可能实现，SQL server可能产生两种可能的错误。如果两种数据类型不能完全兼容（简言之，在两种数据类型之间不能实现隐式或显式转换），SQL server产生下列错误：
|
DECLARE@aINTDECLARE@bDATESET@a = @b--Msg 206, Level 16, State 2, Line 3--Operand type clash: date is incompatible with int |
对于这种情形尝试做一个修正，发生了下列错误：
|
DECLARE@aINTDECLARE@bDATESET@a =CONVERT(INT,@b)--Msg 529, Level 16, State 2, Line 3--Explicit conversion from data type date to int is not allowed. |
另一方面，如果两种数据类型是兼容的，仅仅需要一个显示转换，SQL server产生下列错误：
DECLARE@aINT
DECLARE@bDATETIME
SET@a = @b
--Msg 257, Level 16, State 3, Line 3
--Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. |
通过使用显示convert来解决这个问题：
|
DECLARE@aINT
DECLARE@bDATETIME
SET@a =CONVERT(INT,@b)
|
联接丛书页面中涵盖了一个兼容性矩阵，描述了SQL server如何处理数据类型转换的所有可能性。如果SQL server使用一个隐式转换到一个表达式，它必须选择其中的一个表达式以及一种目标数据类型来实现转换。基本上， 有三种比较表达式选项：
1->转换右边表达式到左边表达式的数据类型
2->转换左边表达式到右边表达式的数据类型
3->将两者转换到第三方数据类型
对于指定的转换，它明确的将右边表达式转换到左边表达式的数据类型。假定不允许这种类型的转换，则指定的转换将失败。下述转换将没有意义：
|
DECLARE@aINTDECLARE@bDATETIMESETCONVERT(DATETIME,@a) = @b--Msg 156, Level 15, State 1, Line 3--Incorrect syntax near the keyword 'convert'. |
实际隐式转换中，SQL server选择一到两种选项中的一个。对于特定的，SQL server将对所有的数据类型分类排列并且从优先级低的向优先级高的数据类型转换。例如，由于整型的优先级低于日期类型，下列查询语句将转换整型变量到日期型：
|
DECLARE@aINTDECLARE@bDATETIMESELECT0WHERE@a = @b |--Compute Scalar(DEFINE:([Expr1000]=(0)))|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@a],0)=[@b])))|—Constant Scan |
这种方法有一些缺陷。需要特别指出的是，一些转换是无损转换，一些是有损转换，而一些则出现转换失败。举例来说，转换一个SMALLINT到INT类型则是一个无损转换。对于这个SAMLLINT值的集合能够被一个作为父集的INT值集来表达。然而转换一个INT到REAL型是有损转换。大多数的INT值不能被一个REAL所表达。
|
DECLARE @a INT DECLARE @b REAL DECLARE @c INT SET @a = 1000000001 SET @b = CONVERT(REAL,@a) SET @c = CONVERT(INT,@b) SELECT @a, @b, @c a b c -------------------- ------------- -------------------- 1000000001 1E+09 1000000000 |
转换一个REAL型到INT整型是一个有损转换（一个整型不能够表示小数部分）而且转换彻底失败：
|
DECLARE @a REAL DECLARE @b INT SET @a = 1e13 SET @b = CONVERT(INT,@a) Msg 232, Level 16, State 3, Line 4 Arithmetic overflow error for type int, value = 9999999827968.000000. |
SQL server使用类型的优先级别试图最小化有损转换的风险或转换失败，但有时候数据丢失或失败是不可避免的，就像上面的实型REAL转为INT整型的例子。
在STRING字符串和NUMERIC数据类型之间的转换（如CHAR或VARCHAR）也是有些疑问。SQL server STRING字符型级别低于NUMERIC数据类型，而且转换的时候STRING字符型总是转换为NUMERIC型。这个转换很容易失败。显然地，有一些包含非NUMERIC的数据不能够被转换为字符型。然而，对这两种类型，反向转换则是更糟糕。有一些方法可以表示一个NUMERIC型作为一个STRING型。下述所有的STRING字符型代表了相同的数字：'1', '?1', '1? ', '+1', '1.0', 和'01'.（其中?代表空格） 。SQL server 转换STRING到NUMERIC是能够确定地选择一种公认的格式，而不去转换那些特殊的STRING到NUMERIC。没有更容易的方式去比较那些并没有公认格式的STRING到NUMERIC类型的转换。举个例子，考虑下面的对比结果：
|
DECLARE @a INT DECLARE @b CHAR(4) SET @a = 1SET @b = @a SELECT @a AS a, @b AS b, CASE WHEN @a = '1 ' THEN 'True' ELSE 'False' END AS [a = '1'], CASE WHEN @a = '+1' THEN 'True' ELSE 'False' END AS [a = '+1'], CASE WHEN @b = '1' THEN 'True' ELSE 'False' END AS [b = '1'], CASE WHEN @b = '+1' THEN 'True' ELSE 'False' END AS [b = '+1'] a b a = '1 ' a = '+1 ' b = '1' b = '+1' ----------- ---- ------- -------- ------- -------- 1 1 True True True False |
注意：上例中当作为一个整型的时候，1=+1，但是做为字符串的时候，'1' <> '+1'。
从上述的三种隐式转换的例子可知，SQL server隐式转换并不会转换两者的数据类型到第三方数据类型。在一些场景中，对于有损转换，转换两者的数据类型到一个超级类型是有可能的。但是目前没有这样的超级类型存在。（至少在目前的SQL版本中）。
有时，这些转换规则并不是非常方便或比较离奇。现实中，它们演变了多年（甚至数十年）并且基于这些推断，一些应用程序已经被开发。现在如果做一个调整将一定会导致一些已经存在的应用程序的失败。
总的来说，无论什么场景的可能性，新的代码中应避免使用不匹配的数据类型，或避免上述那些离奇的错误来最大化提高性能。
|
