Concatenation In SQL Server

Here is a good article on concatenation in SQL Server.  One of the nice features of SQL Server 2012 is the CONCAT function, which makes it a lot easier to read concatenation code, because it handles conversion and null handling automatically.