【SQL】关于case when返回的数据类型优先级的疑问？

Question

请看语句：

select case when 【一个布尔表达式】 then 【一个char】 else 【一个varchar】 end

请留意then和else返回的数据类型不一致，根据数据类型优先原则，varchar高于char，可实践证明这个语句返回的是char，求解释。

Answer 1

是转换为 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')

Answer 2

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

Answer 3

declare @i varchar(1) set @i='1'

select case when @i='1' then '1' else 1 end+'1'

select case when @i<>'1' then '1' else 1 end+'1'

用varchar 和 int做示例比较好理解。 

例如上面两种情况，结果都是2，说明都会转成int。

Answer 4

大概明白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类型

---

给我写信： QQ我：

Answer 5

在网吧里面用傲游浏览器，排版非常奇怪

maco wang大侠那个也是因为

@i='1' 跟@i<>'1' 都需要计算，所以sql会执行else部分，使用else那部分的数据类型

---

给我写信： QQ我：

Answer 6

还是有疑问，请看我的测试：

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型，求解释

Answer 7

可问题出在char和varchar之间

Answer 8

请教邹大，这种样式的执行计划如何得到？我用的SSMS，只知道获取图形化和xml的执行计划

Answer 9

还是有疑问，请看我的测试：

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 语句，而且你的查询要基于随便一个表

Answer 10

看来还真是可能跟版本有关系。“查询要基于随便一个表”我也改了，用的是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得特别注意返回类型未必是预期的了。

非常感谢各位的热心解答！

Answer 11

跟我上面说的一样，还有如果没有用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')

---

给我写信： QQ我：

Answer 12

我的是sqlserver2005

---

给我写信： QQ我：

Answer 13

已经基本判断是版本原因。另外'abc'就是varchar，不是可能

Answer 14

'abc' can be char, nchar, nvarchar, text, ntext, varchar(max), nvarchar(max), ...

Answer 15

rmiao大侠也写一个例子出来看一下，怎麽判断'abc' 是这几种数据类型，还是在不同的情况下都会代表不同的数据类型（在不指定'abc'的数据类型的情况下）

'abc' can be char, nchar, nvarchar, text, ntext, varchar(max), nvarchar(max)

---

给我写信： QQ我：

Answer 16

明白，是can be不是is，'abc'当然能通过转换成为上述类型，但现在说的是不经转换（包括隐式）的'abc'就是varchar

Answer 17

请问LZ怎麽显式转换呢？

---

给我写信： QQ我：

Answer 18

这帖已经跑题了呵呵，转换你懂的，convert或cast，我要表达的是，默认写法的数据类型是确定的：'1' is varchar, N'1' is nvarchar, 1 is int, 1.0 is numeric/decimal