For Example

  1. SELECT CONCAT('Naveen',' Aggarwal')
which results to Naveen Aggarwal

The same can be done using + in the earlier versions

  1. SELECT 'Naveen'+' Aggarwal'
which results to Naveen Aggarwal

But there is a advantage of CONCAT function over '+' in sql server 2012

  1. SELECT 'Naveen'+' Aggarwal'+NULL
When you execute the above, the result is NULL

But the CONCAT function will ignore NULL values

  1. SELECT CONCAT('Naveen',' Aggarwal',NULL)
The result is Naveen Aggarwal

So by using CONCAT function,we need not to worry about handling NULL values.

I hope u like it.