Data types in tables
-
-
Neeraj Prasad Sharma (6/11/2013)
Nice question ..and thanks HUGO for explanation.
Another +1 bites the dust 😉
MCTS | MCITP | Microsoft SQL Server 2008 Administration & Development
MCSA | MCSE | Business Intelligence SQL Server 2012 -
My Collation is also SQL_Latin1_General_CP1_CI_AS.
-
Louis Hillebrand (6/11/2013)
When I run the 2nd query it inserts 2 rows into the table.So my answer would be 'error', '2 rows', '2 rows'.
If I remove the insert from the 2nd query, I get an error.
declare @test-2 table (Value varchar(50));declare @date date ='10-10-2012'
declare @t1 varchar(10)
set @date='10-10-2012'
--insert into @test-2
select '10'
union
select @date --cast as varchar
select * from @test-2
The data type preference depends not only on the union, but takes the table into account also.
Running on SQL 2008 R2
On my SQL Server 2008R2 instance, I get an error from the UNION in the second query whether it's the source for an INSERT or a stand-alone SELECT. The estimated execution plan shows that the UNION will occur first, then the INSERT to the table variable, so there doesn't seem to be any basis to conclude that the application of the data type precedence rules in the UNION is affected at all by the data type of the Values column of the table variable @test. Since you say you can run the INSERT . . . SELECT without error, what does the actual execution plan look like?
Jason Wolfkill
-
This is my execution plan, as you can see, the date is converted to varchar in the Constant Scan.
Louis.
-
Louis Hillebrand (6/11/2013)
This is my execution plan, as you can see, the date is converted to varchar in the Constant Scan.
Louis.
Well, that is interesting!
Whoops - just realized I checked this on a SQL Server 2008 (not R2) instance. The estimated execution plan I get there shows the varchar() value implicitly converted to date. Let me see if I can try it on a 2008R2 instance.
Jason Wolfkill
-
Nice question that shows one of the less obvious pitfalls arising from implicit conversion. Pity the explanation doesn't explain why it works as it does. Of course if you run the code what is going on is obvious from the error messages (which will complain about failure to covert to date, not from), and anyway Hugo has explained it nice and clearly.
I wonder why the language designers decided that type coercion shouldn't be pushed downwards in a situation like this, so that conversion to an intermediate type with too much precedence would not take place. I guess it could be because they reckoned it would make implicit conversion more difficult for many people to understand than it already is.
Tom
-
L' Eomot Inversé (6/11/2013)
Nice question that shows one of the less obvious pitfalls arising from implicit conversion. Pity the explanation doesn't explain why it works as it does. Of course if you run the code what is going on is obvious from the error messages (which will complain about failure to covert to date, not from), and anyway Hugo has explained it nice and clearly.I wonder why the language designers decided that type coercion shouldn't be pushed downwards in a situation like this, so that conversion to an intermediate type with too much precedence would not take place. I guess it could be because they reckoned it would make implicit conversion more difficult for many people to understand than it already is.
Tom, if you look at my conversation in this thread with Louis Hillebrand, it appears that type coercion may be pushed downwards in SQL Server 2008R2 under some set of circumstances - the image of his execution plan shows the date value implicitly converted to varchar() before the merge join operator that accomplishes the UNION.
Jason Wolfkill
-
Tried several options, but always found an implicit conversion in the Constant Scans to the type defined in the table definition.
On my system, the query runs if implicit conversion is possible from the constants to the data-type in the table.
Louis.
-
I'm on SQL2008 R2 and I am not seeing the same results as Louie. This is very interesting behavior from SQL Server.
I wonder if there is a flag or some other setting that is set differently?
-
The database I was testing on was on Compatibility level 80 (SQL Server 2000).
When I change the Compatibility level to 90 or 100 (SQL 2005 / 2008 ) the conversion goes "wrong".
Louis.
-
wolfkillj (6/11/2013)
L' Eomot Inversé (6/11/2013)
Nice question that shows one of the less obvious pitfalls arising from implicit conversion. Pity the explanation doesn't explain why it works as it does. Of course if you run the code what is going on is obvious from the error messages (which will complain about failure to covert to date, not from), and anyway Hugo has explained it nice and clearly.I wonder why the language designers decided that type coercion shouldn't be pushed downwards in a situation like this, so that conversion to an intermediate type with too much precedence would not take place. I guess it could be because they reckoned it would make implicit conversion more difficult for many people to understand than it already is.
Tom, if you look at my conversation in this thread with Louis Hillebrand, it appears that type coercion may be pushed downwards in SQL Server 2008R2 under some set of circumstances - the image of his execution plan shows the date value implicitly converted to varchar() before the merge join operator that accomplishes the UNION.
Yes, you are right. That plan shows clearly that the destination type overrides any precedence issues, i.e. the coercion has been pushed down onto the individual rows of the union before precedence between the rows has been considered. To me that seems the logical thing to do, as you probably guessed from my previous post.
I seem to remember it happening in SQLS 2000 too. But in SQLS 2012 it appears not to happen - I haven't done anything like enough checking to be 100% certain so I will stick to "appears to" - and I think this is by design rather than by accident. If it (no push down) sometimes happens in SQLS 2008 I suspect that will be by design rather than by accident, because I think it happened more there than in SQLS 2000 and imagine that was a change by design, not an accident. I haven't gotten around to installing SQL 2008R2 on my new machine yet, currently I can only check using SQLS 2012 unless I dig the old machine out, so I'm not sure what happens there. All in all I find it irritating that there isn't any nice clear easily found documentation that explains why SQLS 2008 and SQLS 2012 are different in this respect, but maybe that's because I don't read enough of the MS SQL team's blogs (and the blogs of ex-members of that team).
Edit: Just read Louis Hillebrand's last post. That confirms pretty clearly that this was something that changed between SQLS 2000 and subsequent releases. I wonder how much code it broke out there in the real world?
Tom
-
Louis Hillebrand (6/11/2013)
The database I was testing on was on Compatibility level 80 (SQL Server 2000).When I change the Compatibility level to 90 or 100 (SQL 2005 / 2008 ) the conversion goes "wrong".
Ha! That was the pointer I needed to find an explanation.
Check http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.100%29.aspx - the documentation on compatbility level for SQL Server 2008 (the last version that supports level 80). It explicitly mentions this difference in the table for the effect of level 80 vs 90 or higher:
"In an INSERT SELECT from a UNION of different data types, each UNION branch is directly cast to the type of the target column of the INSERT. Even if the union used by itself could fail because of incompatible type conversions, the INSERT SELECT makes the UNION succeed because the branch to the result type of the UNION is never converted."
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/ -
Malls, thanks for your interesting question, and thanks to Hugo for his as-usual excellent analysis.
-
Louis Hillebrand (6/11/2013)
The final select returns '10' and '2012-10-10'Louis.
+1....
First 2 statements give me error.. but Third statement is success and give result set..
There is no any conversion error for individual insert statement...?????
Manik
You cannot get to the top by sitting on your bottom.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply