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 