Danny Ocean (5/10/2013)
Good question, but Explanation is not completed or require more details.
I think hugo can help us.
Unfortunately, I can't. This is not consistent at all. In many other cases, SQL Server will complain when it has to conjure a data type for nulls out of thin air, and it would have made much more sense if it did so for this case as well.
(For example, compare "SELECT COALESCE(NULL, NULL);" with "SELECT COALESCE(NULL, CAST(NULL AS varchar(10)));")
that the reason it defaults to int is purely some internal implementation issue - int is probably first or last in some internal list that happens to be used here.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis