SQLServerCentral.com / Article Discussions by Author / Article Discussions / Discuss content posted by Gopi Sri / T-SQL / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 10:50:47 GMT20RE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[quote][b]pksutha (3/23/2011)[/b][hr]Hi Gopi,select * into TestTable from(select CAST(1 as smallint) as C1, CAST(6 as smallint) as C2unionselect CAST(2 as tinyint),CAST(6 as int)unionselect CAST(3 as bigint),CAST(6 as smallint)unionselect CAST(4 as int),CAST(6 as tinyint)) TNote: Practically i want to see the difference... will u give example for that..[/quote]Hi pksutha,try this query to see the resulting data type:[code="sql"]SELECT RowID ,Col ,SQL_VARIANT_PROPERTY(Col,'BaseType') BaseType ,SQL_VARIANT_PROPERTY(Col,'Precision') Precision ,SQL_VARIANT_PROPERTY(Col,'Scale') Scale ,SQL_VARIANT_PROPERTY(Col,'TotalBytes') TotalBytes ,SQL_VARIANT_PROPERTY(Col,'Collation') Collation ,SQL_VARIANT_PROPERTY(Col,'MaxLength') MaxLengthFROM ( SELECT 1 AS RowID, CAST(1 AS int) ColUNION ALL SELECT 2 AS RowID, CAST('2' AS char) Col)SubQ[/code]And play a little with the datatypes & order of data types in the CASTs to see what the resulting data type will be.Mon, 08 Aug 2011 01:03:19 GMTChristian Buettner-167247RE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxNice question. got a good knowledge. thanks:-)Sat, 02 Apr 2011 01:28:19 GMTReji PRRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxNice post! Thanks!Mon, 28 Mar 2011 23:13:13 GMTmferandaRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxInteresting, Thanks.Mon, 28 Mar 2011 01:21:55 GMTanders-731262RE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxExcellent question. Good point emphasized when inserting with multiple value segments with single insert statement.Sun, 27 Mar 2011 21:46:31 GMTirshadmohideenRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxReally good question. Thanks.Sat, 26 Mar 2011 09:47:24 GMTTomThomsonRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[quote]Note: insert into #test values (2,'D'),('','E') is equal to insert into #test select 2,'D' union select '','E'[/quote]Actually, its like:insert into #test values (2,'D'),('','E') is equal to insert into #test select 2,'D' union [b]all[/b] select '','E'Thu, 24 Mar 2011 13:00:17 GMTPeter E. KiersteadRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxHi, It's really very good question. thanks for sharing.Thu, 24 Mar 2011 07:03:01 GMTDanny OceanRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxgreated questionWed, 23 Mar 2011 08:12:59 GMTmalleswarareddy_mRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxNice question, thanks.Wed, 23 Mar 2011 02:57:47 GMTKoen VerbeeckRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxAs mentioned earlier, after executing the above code, Go toDataBases -&gt; SystemDataBases -&gt; tempdb -&gt; TestTable -&gt; Columns . You can see that column C1 is created with type bigint and C2 is created with type int based on the highest precedence in the select list.I think this could be the practical example you are expecting.ThanksGopiWed, 23 Mar 2011 02:21:57 GMTGopi SRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxHi Gopi,select * into TestTable from(select CAST(1 as smallint) as C1, CAST(6 as smallint) as C2unionselect CAST(2 as tinyint),CAST(6 as int)unionselect CAST(3 as bigint),CAST(6 as smallint)unionselect CAST(4 as int),CAST(6 as tinyint)) TNote: Practically i want to see the difference... will u give example for that..Wed, 23 Mar 2011 01:27:18 GMTpksuthaRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxI am sorry i couldn't get your question. Can you pls explain you question in detail.ThanksWed, 23 Mar 2011 00:32:14 GMTGopi SRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxHi Gopi,select * into TestTable from(select CAST(1 as smallint) as C1, CAST(6 as smallint) as C2unionselect CAST(2 as tinyint),CAST(6 as int)unionselect CAST(3 as bigint),CAST(6 as smallint)unionselect CAST(4 as int),CAST(6 as tinyint)) TNote: Good only....But u've given only these are the orderwise datatype based on size . but i need how i can come to know query wise.. plz will u give example and we must see the difference in that example visually.Wed, 23 Mar 2011 00:20:59 GMTpksuthaRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxBradley, You are right. The datatype is determined by the order of precedence as explained in http://msdn.microsoft.com/en-us/library/ms190309.aspx . Thanks for pointing out this and i apologize everyone for making this mistake.We can verify this by small example.For integer datatype the order of precedence level is 1: bigint (highest precedence)2: int3: smallint4: tinyint (lowest precedence) .Note: For demo i have choosen only the above 4 datatype.After running the below code Go to DataBases -&gt; SystemDataBases -&gt; tempdb -&gt; TestTable -&gt; Columns . Its clear that column C1 is created with type bigint and C2 is created with type int based on the highest precedence in the select list.-- Sample codeuse tempdbgoselect * into TestTable from(select CAST(1 as smallint) as C1, CAST(6 as smallint) as C2unionselect CAST(2 as tinyint),CAST(6 as int)unionselect CAST(3 as bigint),CAST(6 as smallint)unionselect CAST(4 as int),CAST(6 as tinyint)) TThanks again.GopiWed, 23 Mar 2011 00:04:24 GMTGopi SRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxThis question is worth more than a point :-DTue, 22 Mar 2011 12:02:17 GMTM&MRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxthanks for the questionTue, 22 Mar 2011 10:12:41 GMTSQLRNNRRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxGreat question.UMG,Thanks for the link, that's good to know for sure.Tue, 22 Mar 2011 09:56:59 GMTBradley DeemRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[quote][b]Bradley Deem (3/22/2011)[/b][hr][quote][b]UMG Developer (3/21/2011)[/b][hr]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.[/quote]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.[/quote]Bradley, Thanks for pointing that out, of course I should have known better. Here is a link to the Table Value Constructor in BOL that mentions the data type precedence: [url]http://msdn.microsoft.com/en-us/library/dd776382.aspx[/url]Tue, 22 Mar 2011 09:55:25 GMTUMG DeveloperRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxThis was an excellent learning question.Thank youTue, 22 Mar 2011 09:47:51 GMTc00578RE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxHi Gopi,Fantastic answer and Explanation..goodgoodselect * into #D1 from (select '1' as c1,'A' as c2unionselect '','B') Tselect * from #D1goselect * into #D2 from (select 1 as c1,'A' as c2unionselect '','B') Tselect * from #D2drop table #D1drop table #D2Note : Really and very exact explanation....i understood very nice...thank u .Tue, 22 Mar 2011 09:47:20 GMTpksuthaRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[quote][b]Cliff Jones (3/22/2011)[/b][hr]Interesting, I did not realize that the Table Value Constructor behaved like a UNION. It makes sense now that it has been pointed out.[/quote]Great Question. I also have not used SQL 2008 new features enough to see this happen yet. Thanks for pointing that out.Tue, 22 Mar 2011 08:48:10 GMTSanDroidRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[quote][b]Hugo Kornelis (3/22/2011)[/b][hr]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.[/quote]Except the code was nicely color coded so the text was a different color than the integers.Tue, 22 Mar 2011 08:27:30 GMTcengland0RE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxInteresting, I did not realize that the Table Value Constructor behaved like a UNION. It makes sense now that it has been pointed out.Tue, 22 Mar 2011 08:04:19 GMTCliff JonesRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[quote][b]Bradley Deem (3/22/2011)[/b][hr][quote][b]UMG Developer (3/21/2011)[/b][hr]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. [/quote]Thanks Bradley. Quite interesting learning for me.Tue, 22 Mar 2011 07:55:23 GMTtejaswini.patilRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[quote][b]UMG Developer (3/21/2011)[/b][hr]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.[/quote]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.[code="sql"]create table #test(c1 varchar(10), c2 varchar(10));insert into #test select '','I' union allselect 3,'J';select * from #test;[/code]Which returnsc1 c20 I3 JData Type Prescendance [url]http://msdn.microsoft.com/en-us/library/ms190309.aspx[/url]Tue, 22 Mar 2011 07:46:25 GMTBradley DeemRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxThanks for the question - clearly highlights the pitfalls in implicit conversions.Tue, 22 Mar 2011 07:43:31 GMTDuncan PrydeRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[i]Gopi&gt;&gt;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.[/i][b]The examples in your question illustrate this point perfectly. Yet another example of the dangers of implicit conversion.[/b][i]Gopi&gt;&gt;Hope this is helpful ?[/i][b]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.[/b]Tue, 22 Mar 2011 07:36:48 GMTRose BudRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxA 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.[quote][b]Gopinath Srirangan (3/22/2011)[/b][hr]Still you have same issue in SQL Server 2005 using old method.[/quote]True. And even in a less elaborate syntax than what you post:[code]create table #test(c1 varchar(10), c2 varchar(10));goinsert into #test select '1','A' union allselect '','B';insert into #test select 2,'D' union allselect '','E';insert into #test select '','G' union allselect '','H';goselect * from #test;[/code]Tue, 22 Mar 2011 07:12:54 GMTHugo KornelisRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxThat new syntax in 2008 is handy. I'll have to try it out. Nice to learn something new. Thanks.Tue, 22 Mar 2011 07:12:10 GMTOCTomRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxHi 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 c2unionselect '','B') Tselect * from #D1goselect * into #D2 from (select 1 as c1,'A' as c2unionselect '','B') Tselect * from #D2drop table #D1drop table #D2Implicit conversion matters both in 2005 and 2008 when using union in the select before inserting the records.Tue, 22 Mar 2011 06:23:38 GMTGopi SRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxya 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 youTue, 22 Mar 2011 06:19:35 GMTpksuthaRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxGood question, thanks.This serves as a gentle reminder of the dangers of relying on implicit conversion.Tue, 22 Mar 2011 06:06:43 GMTStewart "Arturius" CampbellRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxFor 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 sqlserver2005Tue, 22 Mar 2011 05:56:40 GMTpksuthaRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxHi Gopi,i was explaing about insertion syntax,not about selection process. In both 2005 and 2008 will display ans for selection command for insertion command.bcoz sqlseerver 2005 doesn't support this syntax like insert into #test values('1','A'),(' ','B') With Regards SuthaTue, 22 Mar 2011 05:52:41 GMTpksuthaRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxHi pksutha,Let me explain little more..insert into #test values ('1','A'), ('','B')is not same as below.insert into #test values('1','A')insert into #test values ('','B')The former insert statement in introduced in 2008 and the later is the usual code that we use often. To be compatible in both, pls run the below code either in 2005 or 2008 and compare the results.select '1' as c1,'A' as c2unionselect '','B'Result1: c1 c21 A Bgoselect 1 as c1,'A' as c2unionselect '','B'Result2:c1 c21 A0 BThe 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.From the above example , first result set has c1 [varchar(1)] , C2 [varchar(1)]second result set has c1 [int, C2 [varchar(1)]Hope this is helpful ?ThanksGopiTue, 22 Mar 2011 05:36:18 GMTGopi SRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxIn SQL Server 2008 the INSERT statement has been enhanced which enables to insert multiple records using the VALUE clause in a single INSERT statement.insert into #test values (2,'D'), ('','E')Above INSERT statement will insert two records.In first set of values, 2 is not embedded in single quotes and that is why the data type is considered as "int". SQL Server considers that data type for the first value of all records being inserted in that INSERT statement.First set of values inserts a record as 2, DWhile inserting second record, the first value '' is implicitly converted as 0 so second set of values inserts a record as 0, E.This question gave us a learning that we have to be careful about implicit conversion that SQL Server does.Tue, 22 Mar 2011 05:16:47 GMTtejaswini.patilRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxsorry ,i'm not getting .i got first answer in sql server2008Tue, 22 Mar 2011 03:48:05 GMTkannappanvrmkRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspxAnd are you sure this below code executed successfully in 2005 ?This is not a correct syntax in sqlserver2005 which is below given queryinsert into #test values('1','A'),('','B')correct answer:insert into #test values('1','A')insert into #test values ('','B')this is the way to insert the values to the particular table in sqlserver2005Tue, 22 Mar 2011 03:45:21 GMTpksuthaRE: T-SQLhttp://www.sqlservercentral.com/Forums/Topic1081713-2867-1.aspx[quote][b]Gopinath Srirangan (3/22/2011)[/b][hr]And are you sure this below code executed successfully in 2005 ?insert into #test values('1','A'),('','B')Thanks[/quote]It will not work in 2005. That's the server I use daily and I wish this syntax would work because it could simplify my job significantly when inserting a bunch of values manually.Tue, 22 Mar 2011 03:21:12 GMTcengland0