Dave62 (8/22/2013)
But apparently whoever added the new CONCAT function to SQL Server 2012 is in agreement with my interpretation because the result is the known values.
I think it's more like one Microsoft employee ripping out CONCAT_NULL_YIELDS_NULL because it violates all relational theory, and the guy at the next desk putting in CONCAT because it's easier for some cases.
I've had to work with counters that corresponded to C# zero-based arrays. So "3" represents four elements, and if you combine two of those arrays, you have "7" (representing eight) elements. For that specific use case, it would have been practical if 3 + 3 would evaluate to 7. If someone would redefine addition to work that way, it would save me the effort of having to explicitly write "+1" to my additions. Okay, it may fly in the face of all mathematical theory - but it is SO PRACTICAL (in that specific situation).
Does the request to redefine addition this way sound absurd to you? Well, the request to redefine concatenation of NULL values is exactly like that.
tilew-948340 (8/22/2013)
Hugo, you should write a book!
Already done. Okay, not a whole book. But I did contribute a chapter for Adam Machanics "Expert SQL Server 2005 Development", two chapters for "SQL Server MVP Deep Dives, volume 1", and one chapter for "SQL Server MVP Deep Dives volume 2".
(I guess I should invest the time to add links to these books on Amazon.com, but I should really start on my work now")