none
【SQL】关于case when返回的数据类型优先级的疑问? RRS feed

  • 问题

  • 请看语句:

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

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


    2012年10月9日 9:15

答案

  • 还是有疑问,请看我的测试:

    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
    2012年10月10日 4:22

全部回复

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

    2012年10月9日 11:41
  • 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

    2012年10月9日 11:46
  • 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。

    2012年10月9日 12:51
    版主
  • 大概明白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我:点击这里给我发消息


    2012年10月9日 14:38
  • 在网吧里面用傲游浏览器,排版非常奇怪

    maco wang大侠那个也是因为

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


    给我写信: QQ我:点击这里给我发消息

    2012年10月9日 14:41
  • 还是有疑问,请看我的测试:

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

    2012年10月10日 2:23
  • 可问题出在char和varchar之间
    2012年10月10日 2:25
  • 请教邹大,这种样式的执行计划如何得到?我用的SSMS,只知道获取图形化和xml的执行计划

    2012年10月10日 2:31
  • 还是有疑问,请看我的测试:

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

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

    2012年10月10日 5:16
  • 跟我上面说的一样,还有如果没有用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我:点击这里给我发消息

    2012年10月10日 5:19
  • 我的是sqlserver2005

    给我写信: QQ我:点击这里给我发消息

    2012年10月10日 5:25
  • 已经基本判断是版本原因。另外'abc'就是varchar,不是可能
    2012年10月10日 6:03
  • 'abc' can be char, nchar, nvarchar, text, ntext, varchar(max), nvarchar(max), ...
    2012年10月10日 13:17
  • rmiao大侠也写一个例子出来看一下,怎麽判断'abc' 是这几种数据类型,还是在不同的情况下都会代表不同的数据类型(在不指定'abc'的数据类型的情况下)

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


    给我写信: QQ我:点击这里给我发消息


    2012年10月11日 1:31
  • 明白,是can be不是is,'abc'当然能通过转换成为上述类型,但现在说的是不经转换(包括隐式)的'abc'就是varchar

    2012年10月11日 2:01
  • 请问LZ怎麽显式转换呢?


    给我写信: QQ我:点击这里给我发消息

    2012年10月11日 15:46
  • 这帖已经跑题了呵呵,转换你懂的,convert或cast,我要表达的是,默认写法的数据类型是确定的:'1' is varchar, N'1' is nvarchar, 1 is int, 1.0 is numeric/decimal
    2012年10月12日 1:02