In my earlier article, I wrote about different aspect of Concat function introduced in SQL Server 2012. In this article, we will compare the Concat function with the traditional concatenation techniques and analyze the performance.
Method 1 : Using ISNULL With + (String Concatenation)
Use AdventureWorks2012 GO Select ISNULL([firstName],'') + ' ' + ISNULL([MiddleName],'') + ' ' + ISNULL([LastName],'') from [HumanResources].[vEmployee]
Method 2 : Using COALESCE With + (String Concatenation)
Use AdventureWorks2012 GO Select COALESCE([firstName],'') + ' ' + COALESCE([MiddleName],'') + ' ' + COALESCE([LastName],'') from [HumanResources].[vEmployee]
Method 3 : Using Concat function of SQL Server 2012
Use AdventureWorks2012 GO Select CONCAT ( [firstName] , ' ' , [MiddleName] , ' ' , [LastName]) from [HumanResources].[vEmployee]
All of the above methods will give you the same result sets but lets view their performance given below.
|
Method |
Concatenation expression |
CPU Time |
Elapsed Time |
|
1 |
ISNULL([firstName],”) + ‘ ‘+ ISNULL([MiddleName],”) + ‘ ‘ + ISNULL([LastName],”) |
141 ms |
2234 ms |
|
2 |
COALESCE([firstName],”) + ‘ ‘ + COALESCE([MiddleName],”) + ‘ ‘ + COALESCE([LastName],”) |
187 ms |
3185 ms |
|
3 |
CONCAT( [firstName], ‘ ‘, [MiddleName], ‘ ‘, [LastName]) |
94 ms |
1821 ms |
Conclusion :
It is needless to stress that Concat function is much faster than the other methods.
Note : The above queries have been tested on ~100K records.
SQL Server Portal 
Nice tip, how does the CONCAT function handle the possibility of NULL values in the item list?
Hi Mike,
Thank you, if there is any null value in any parameter of this function, it automatically replaces it to empty string.
Imran
I wonder how SQL achieves the faster speed for CONCAT() when, internally, it has to do the same just like the ISNULL() call ?
The Coalesce() will be a bit slower than IsNull() because, having greater functionality, internally it has more code to navigate. I guess.
Hi jamie,
In my opinion, the major reason behind the performance is that in all the above methods, we called ISNULL 3 times, Coalesce 3 times , But Concat function 1 time only.
Imran
Really, using COALESCE for concatenation in pre-2012 SQL versions has already been shown to be less than optimal. Using FOR XML PATH has been the recommended method:
SELECT DISTINCT
STUFF((
SELECT TOP(10)
‘|’+CAST(f.FooID AS INT)
FROM
dbo.Foo AS f
WHERE
f.FooID > 25
FOR XML PATH(”)
),1,1,”)
FROM
dbo.Foo
Can you compare this method to the new CONCAT operator in 2012?
Thanks
Steven Willis
Hi Steven,
Absolutely right, because I also recommended this method a while ago in my article (https://raresql.com/2012/12/18/sql-server-create-comma-separated-list-from-table/).
But there is a difference between Both methods, Let me explain it.
XML Path :
This method can concatenate multiple rows and make it single column (with delimiter it any).
Concat Function :
This method concatenate multiple columns and make it a single column.
Thank you
Imran
[…] and automatic concatenation using CONCAT function. I would recommend Method 2 because in my earlier article I found that CONCAT function is much faster than the normal […]
Can i use this CONCAT function in Sql server 2008 ?
Hi,
Concat function is only compatible with SQL Server 2012 and above.
[…] and automatic concatenation using CONCAT function. I would recommend Method 2 because in my earlier article I found that CONCAT function is much faster than the normal […]
[…] Conclusion : I generally recommend solution 2 because as you can see that there is no difference in output between solution 1 and solution 2 but there is a remarkable difference between both solutions in performance. You can get the performance details from here. […]