CONCAT() is new string function in SQL Server Denali. It returns a string that is the result of concatenating two or more string values (or values that can be converted to string).
Syntax: CONCAT (string_value1, string_value2 [, string_valueN ] )

With CONCAT function, you can concatenate between 2 and 254 values. If you try to use CONCAT with only one value or with more than 254 values you will get an error.

SELECT CONCAT('SQL Server') AS Result

Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.

Simple examples:

SELECT CONCAT('SQL', 'Server', 'Denali') AS Result1
/*
Result1
---------------
SQLServerDenali
*/

DECLARE @a varchar(10) = 'SQL'
DECLARE @b varchar(10) = 'Server'
DECLARE @c varchar(10) = 'Denali'
SELECT CONCAT(@a, @b, @c) AS Result2
/*
Result2
------------------------------
SQLServerDenali
*/

USE AdventureWorks2008R2
GO
SELECT TOP 5 CONCAT(FirstName, LastName) AS FullName
FROM Person.Person
/*
FullName
----------------------
SyedAbbas
CatherineAbel
KimAbercrombie
KimAbercrombie
KimAbercrombie
*/

It’s easy to work with CONCAT function, but as you can see there is no string delimiter between values. To get proper FullName in the last example you will need to add space between FirstName and LastName.

USE AdventureWorks2008R2
GO
SELECT TOP 5 CONCAT(FirstName, ' ', LastName) AS FullName
FROM Person.Person
/*
FullName
------------------------
Syed Abbas
Catherine Abel
Kim Abercrombie
Kim Abercrombie
Kim Abercrombie
*/

CONCAT function has 2 advantages compared to the concatenating of strings using the “+” operator:
1. NULL values are implicitly converted to an empty string
2. All arguments are implicitly converted to string

CONCAT() and NULL arguments

CONCAT function automatically converts all NULL arguments to an empty string. It’s great for T-SQL developers. In next examples you can see the difference between CONCAT function and concatenation of strings using the “+” operator.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = NULL
DECLARE @c varchar(10) = 'Denali'
-- CONCAT implicitly convert NULL to an empty string
SELECT CONCAT(@a, @b, @c) AS Result1

-- If you use "+" operator with NULL result is NULL
SELECT @a + @b + @c AS Result2

-- To get the same result with "+" operator you need to deal with NULL values
SELECT @a + ISNULL(@b, '') + @c AS Result3
/*
Result1
------------------------------
SQL ServerDenali
Result2
------------------------------
NULL
Result3
------------------------------
SQL ServerDenali
*/

If all the arguments are null, then an empty string of type varchar(1) is returned.

CONCAT() and non-string arguments

CONCAT function implicitly converts all non-string arguments to string data types (if conversion is possible) and then concatenate values.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = 'Denali'
DECLARE @c int = 2011
DECLARE @d datetime = getdate()
SELECT CONCAT(@a, @b, @c, @d) AS Result1
/*
Result1
------------------------------------------
SQL ServerDenali2011Aug 27 2011  9:42PM
*/

As you can see, @c and @d are implicitly converted to string and you get result back. If you use same example with “+” operator you will get an error.
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value ‘SQL ServerDenali’ to data type int.

To get the same result with “+” operator you need to convert @c and ©d values to string data types.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = 'Denali'
DECLARE @c int = 2011
DECLARE @d datetime = getdate()
SELECT @a + @b + CONVERT(varchar(10),@c) + CONVERT(varchar(20),@d) AS Result2
/*
Result2
--------------------------------------------------
SQL ServerDenali2011Aug 27 2011  9:42PM
*/

As you can see, concatenating string is much easier with CONCAT function.  What you think?