T-SQL

Question

Post reply

T-SQL

March 22, 2011 at 6:06 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 48 total)

You must be logged in to reply to this topic. Login to reply

pksutha SSC Enthusiast Points: 171 More actions · Answer 1

For datatype , this doubt clarification i've posted one answered already.

varchar accepts both number and character and without single quote number also like (1,'b') or ('1','b')

both possible in sqlserver2005

pksutha SSC Enthusiast Points: 171 More actions · Answer 2

ya i'm Agreeing this.

First of all insert into #test values(2,'A'),(' ' ,'B') this is not there in sqlserver 2005.

So now we can't consider data type issue.

IF u go with sqlserver2008 ,yes of course we need to consider about data types.

Thank you

Gopi S Ten Centuries Points: 1091 More actions · Answer 3

Hi Sutha,

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

Try this and compare the results.

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

Implicit conversion matters both in 2005 and 2008 when using union in the select before inserting the records.

OCTom SSChampion Points: 11785 More actions · Answer 4

That new syntax in 2008 is handy. I'll have to try it out. Nice to learn something new. Thanks.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 5

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/

Rose Bud Hall of Fame Points: 3147 More actions · Answer 6

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.

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 7

Thanks for the question - clearly highlights the pitfalls in implicit conversions.

Bradley Deem SSCrazy Points: 2565 More actions · Answer 8

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

tejaswini.patil SSCommitted Points: 1792 More actions · Answer 9

Bradley Deem (3/22/2011)

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.

Cliff Jones SSChampion Points: 10547 More actions · Answer 10

Interesting, I did not realize that the Table Value Constructor behaved like a UNION. It makes sense now that it has been pointed out.

cengland0 SSCertifiable Points: 6102 More actions · Answer 11

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.

SanDroid SSChampion Points: 10068 More actions · Answer 12

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.

pksutha SSC Enthusiast Points: 171 More actions · Answer 13

Hi Gopi,

Fantastic answer and Explanation..

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 .

c00578 SSChasing Mays Points: 619 More actions · Answer 14

c00578

SSChasing Mays

Points: 619

March 22, 2011 at 9:47 am

#1301952

This was an excellent learning question.

Thank you