• A nice question. Thanks!

    My only gripe (yes, I have one - nopt a big one, though) is that the difference between 1 and '1' is easily lost. I almost chose option 1 because I thought the three INSERT blocks were all identitical. Since the goal of the question was to test understanding of implicit conversions, not detailed proofreading, a comment or other explicit note about these subtle differences would have been nice.

    Gopinath Srirangan (3/22/2011)


    Still you have same issue in SQL Server 2005 using old method.

    True. And even in a less elaborate syntax than what you post:

    create table #test(c1 varchar(10), c2 varchar(10));

    go

    insert into #test

    select '1','A' union all

    select '','B';

    insert into #test

    select 2,'D' union all

    select '','E';

    insert into #test

    select '','G' union all

    select '','H';

    go

    select * from #test;


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/