Neil Gelder has a look at the CONCAT() function in SQL Server 2012:
This function removes all need to convert values just to concatenate text and string values together, but for some reason whenever I see code posted on various websites, even from SQL Server MVP’s they seem never to utilise this function.
One excellent use for this is when you attempting to created the SQL Statement for a dynamic SQL execution. Instead of the usual conversions and + signs joining the text and parameters together its far simpler.
CONCAT is one of my favorite T-SQL enhancements from 2012 (after window functions).