Feeds:
Posts
Comments

Posts Tagged ‘CONCAT Function’

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.

Read Full Post »