What is the Difference Between + And Concat Function In SQL?

concat vs plus

The concat function was first introduced to SQL Server 2012, so prior versions of SQL need to use the + operator to concatenate strings.

Exam Tip!

The key difference between the two is the fact that the plus operator will by default return a null result when fed a null value. The concat function however, will treat a null as an empty string.
.
select null + ' some ' + 'text'

select concat(null, ' some ' ,'text')

Is concat better performance than +?

When using SQL 2012 or above you have the choice to use either + or concat. The recommended option is to use the concat function.

The reason is that the syntax is easier to follow, and the code generally cleaner. As far as actual performance goes, we have not uncovered any difference between the two methods.

Example of SQL Concatenation with +

Here is a basic example of concatentation using the + operator.

SELECT 'This ' + 'is' + ' concatenation'

Example of concatenation with concat function in sql

To do the same thing with the concat keyword is simply..

SELECT concat('This ','is',' concatenation')

Error ‘concat’ is not a recognized built-in function name.

That is the error that you get, when you try to run the concat function on SQL2008 or below.

As the function does not exist, you are going to get that error!

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

1 Comment

  1. You need to do more performance testing, perhaps with larger data sets. In my case, replacing CONCAT w/ + in a stored proc meant going from over a minute to return results to less than a second.

Leave a Reply