积极答复者
【SQL】关于case when返回的数据类型优先级的疑问?

问题
答案
-
还是有疑问,请看我的测试:
DECLARE @i INT, @a SQL_VARIANT, @b SQL_VARIANT
SET @i = 1
SELECT @a = CASE WHEN @i = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END --按说需要走case when
SELECT @b = CASE WHEN 1 = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END
SELECT SQL_VARIANT_PROPERTY(@a,'BaseType')
SELECT SQL_VARIANT_PROPERTY(@b,'BaseType')返回:@a和@b都是char型,求解释
我查询的结果,前者是 varchar, 后者是 char
不知道你用的什么版本
我的示例中有如何得到计划的,就是前面有一条 SET STATISTICS PROFILE ON 语句,而且你的查询要基于随便一个表
- 已标记为答案 ahdung_AI 2012年10月10日 5:16
全部回复
-
是转换为 varchar 的,估计是你的 CASE WHEN 是确定的,导致不需要判断整个表达式来确定结果类型吧
参考下面的示例
SET STATISTICS PROFILE ON; GO DECLARE @flag int, @a sql_variant, @b sql_variant ; SET @flag = 1; SELECT TOP(1) @a = CASE WHEN @flag = 1 THEN CONVERT(char(1), 'a') ELSE CONVERT(varchar(1), 'b') END, @b = CASE WHEN 1 = 1 THEN CONVERT(char(1), 'a') ELSE CONVERT(varchar(1), 'b') END FROM sys.tables ; SELECT SQL_VARIANT_PROPERTY(@a, 'basetype'), SQL_VARIANT_PROPERTY(@b, 'basetype')
-
SELECT TOP(1) @a = CASE WHEN @flag = 1 THEN CONVERT(char(1), 'a') ELSE CONVERT(varchar(1), 'b') END, @b = CASE WHEN 1 = 1 THEN CONVERT(char(1), 'a') ELSE CONVERT(varchar(1), 'b') END FROM sys.tables ;
|--Compute Scalar(DEFINE:([Expr1047]=CONVERT_IMPLICIT(sql_variant,CASE WHEN [@flag]=(1) THEN 'a' ELSE 'b' END,0), [Expr1048]=CONVERT_IMPLICIT(sql_variant,'a',0)))
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Left Outer Join, WHERE:([master].[sys].[sysidxstats].[id] as [lob].[id]=[master].[sys].[sysschobjs].[id] as [o].[id]))
|--Filter(WHERE:(has_access('CO',[master].[sys].[sysschobjs].[id] as [o].[id])=(1)))
| |--Clustered Index Scan(OBJECT:([master].[sys].[sysschobjs].[clst] AS [o]), WHERE:([master].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [master].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1) AND [master].[sys].[sysschobjs].[type] as [o].[type]='U'))
|--Index Scan(OBJECT:([master].[sys].[sysidxstats].[nc] AS [lob]), WHERE:([master].[sys].[sysidxstats].[indid] as [lob].[indid]<=(1)))---------------------------------------
查询结果可以看到
第一个 CASE 输出的结果类型为 varchar
第二个的输出类型为 char
分析执行计划可以看出,第2个的结果其实是可以直接判断的,所以执行计划根本就没有走 CASE WHEN 处理
第一个确实是需要判断,所以它去评估了 WHEN 和 ELSE 的表达式,并且根据优先组确定出了结果类型为 varchar
-
大概明白zhoujian大侠的意思,
SELECT TOP(1) @a= CASE WHEN @flag = 1 THEN CONVERT(char(1),'a') ELSE CONVERT(varchar(1),'b') END, @b = CASE WHEN 1 =1 THEN CONVERT(char(1),'a') ELSE CONVERT(varchar(1),'b') END FROM sys.tables
@flag= 1 :需要计算@flag= 1 ,所以sql会执行else部分,使用else部分的数据类型,不管then那部分的数据类型
1= 1 :不需要计算1= 1 ,他永远为真,所以sql没有执行else部分,没有判断else部分的数据类型,所以返回char类型
- 已编辑 Steven.桦仔 2012年10月9日 14:43
-
还是有疑问,请看我的测试:
DECLARE @i INT, @a SQL_VARIANT, @b SQL_VARIANT
SET @i = 1
SELECT @a = CASE WHEN @i = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END --按说需要走case when
SELECT @b = CASE WHEN 1 = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END
SELECT SQL_VARIANT_PROPERTY(@a,'BaseType')
SELECT SQL_VARIANT_PROPERTY(@b,'BaseType')返回:@a和@b都是char型,求解释
-
还是有疑问,请看我的测试:
DECLARE @i INT, @a SQL_VARIANT, @b SQL_VARIANT
SET @i = 1
SELECT @a = CASE WHEN @i = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END --按说需要走case when
SELECT @b = CASE WHEN 1 = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END
SELECT SQL_VARIANT_PROPERTY(@a,'BaseType')
SELECT SQL_VARIANT_PROPERTY(@b,'BaseType')返回:@a和@b都是char型,求解释
我查询的结果,前者是 varchar, 后者是 char
不知道你用的什么版本
我的示例中有如何得到计划的,就是前面有一条 SET STATISTICS PROFILE ON 语句,而且你的查询要基于随便一个表
- 已标记为答案 ahdung_AI 2012年10月10日 5:16
-
看来还真是可能跟版本有关系。“查询要基于随便一个表”我也改了,用的是SQL 2008 R2,SSMS用过2008 R2和2012,结果一样。我的测试反馈:
SELECT TOP 1 @a = CASE WHEN @i = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END, @b = CASE WHEN 1 = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END FROM TDefEmp
|--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(sql_variant,'abc ',0), [Expr1006]=CONVERT_IMPLICIT(sql_variant,'abc ',0)))
|--Top(TOP EXPRESSION:((1)))
|--Index Scan(OBJECT:([xxx].[dbo].[TDefEmp].[AK_TDefEmp_EmpName]))说明无论是@i=1还是1=1,在这个版本下都已经是直接解析,都没走case when,所以都返回char。从性能角度看是好的改变,但却带来这个问题,看来以后用case when得特别注意返回类型未必是预期的了。
非常感谢各位的热心解答!
-
跟我上面说的一样,还有如果没有用CAST('abc' AS VARCHAR(10)) 而是直接使用'abc' 估计sqlserver会把'abc'当成是varchar ,即是说如果'abc'不指定数据类型的话,
sqlserver会把'abc'指定为varchar数据类型
下面的两段sql执行的结果都是一样的
SET STATISTICS PROFILE ON DECLARE @i INT, @a SQL_VARIANT, @b SQL_VARIANT SET @i = 1 SELECT @a = CASE WHEN @i = 1 THEN CAST('abc' AS CHAR(5)) ELSE CAST('abc' AS VARCHAR(10)) END --按说需要走case when SELECT @b = CASE WHEN 1 = 1 THEN CAST('abc' AS CHAR(5)) ELSE CAST('abc' AS VARCHAR(10)) END SELECT SQL_VARIANT_PROPERTY(@a,'BaseType') SELECT SQL_VARIANT_PROPERTY(@b,'BaseType') ----------------------------------------------------------------------------------------------------- SET STATISTICS PROFILE ON DECLARE @i INT, @a SQL_VARIANT, @b SQL_VARIANT SET @i = 1 SELECT @a = CASE WHEN @i = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END --按说需要走case when SELECT @b = CASE WHEN 1 = 1 THEN CAST('abc' AS CHAR(5)) ELSE 'abc' END SELECT SQL_VARIANT_PROPERTY(@a,'BaseType') SELECT SQL_VARIANT_PROPERTY(@b,'BaseType')