Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

T-SQL Expand / Collapse
Author
Message
Posted Tuesday, March 22, 2011 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 6,128, Visits: 8,393
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1081964
Posted Tuesday, March 22, 2011 7:36 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:15 AM
Points: 1,447, Visits: 1,059
Gopi>>The important thing that i want to bring out is the datatype of the final result set is based on the first statement in the insert list.

The examples in your question illustrate this point perfectly. Yet another example of the dangers of implicit conversion.

Gopi>>Hope this is helpful ?

More than you thought: You may consider me ignorant, but I have been using 2008 for a year and I never knew you could insert multiple records with one single INSERT statement. Furthermore, I would not have guessed that doing so would behave like a union. Your question and explanation drive these points home quite well. Thank you.

Post #1081980
Posted Tuesday, March 22, 2011 7:43 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
Thanks for the question - clearly highlights the pitfalls in implicit conversions.
Post #1081981
Posted Tuesday, March 22, 2011 7:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 4:10 PM
Points: 554, Visits: 1,208
UMG Developer (3/21/2011)
Interesting question, thanks, and a good reason you should always specify the data type for hardcoded items in the first set of values or in the first query when using UNION.


I'm not sure this is true. It looks to me Data Type Precendence determines the data type not the order in the UNION or VALUES clause. Consider the following.

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

insert into #test
select '','I' union all
select 3,'J';

select * from #test;

Which returns
c1 c2
0 I
3 J

Data Type Prescendance http://msdn.microsoft.com/en-us/library/ms190309.aspx
Post #1081983
Posted Tuesday, March 22, 2011 7:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 1:59 AM
Points: 1,042, Visits: 234
Bradley Deem (3/22/2011)
[quote]UMG Developer (3/21/2011)

I'm not sure this is true. It looks to me Data Type Precendence determines the data type not the order in the UNION or VALUES clause.


Thanks Bradley. Quite interesting learning for me.
Post #1081994
Posted Tuesday, March 22, 2011 8:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
Interesting, I did not realize that the Table Value Constructor behaved like a UNION. It makes sense now that it has been pointed out.
Post #1081999
Posted Tuesday, March 22, 2011 8:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Hugo Kornelis (3/22/2011)
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.

Except the code was nicely color coded so the text was a different color than the integers.
Post #1082014
Posted Tuesday, March 22, 2011 8:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
Cliff Jones (3/22/2011)
Interesting, I did not realize that the Table Value Constructor behaved like a UNION. It makes sense now that it has been pointed out.


Great Question. I also have not used SQL 2008 new features enough to see this happen yet.
Thanks for pointing that out.
Post #1082034
Posted Tuesday, March 22, 2011 9:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:43 AM
Points: 47, Visits: 57
Hi Gopi,



Fantastic answer and Explanation..
good
good

select * into #D1 from (
select '1' as c1,'A' as c2
union
select '','B'
) T

select * from #D1
go

select * into #D2 from (
select 1 as c1,'A' as c2
union
select '','B'
) T
select * from #D2

drop table #D1
drop table #D2




Note : Really and very exact explanation....

i understood very nice...

thank u .
Post #1082076
Posted Tuesday, March 22, 2011 9:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 16, 2011 1:46 PM
Points: 487, Visits: 70
This was an excellent learning question.

Thank you
Post #1082078
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse