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. 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.

  2. And one more thing while using concat function you can concat the different data types without converstion/cast, while using the + operator you can not concat the different datatype values.


    select concat(1.5 ,’text’, 5) -> Result: 1.5text5

    select 1.5 + 5 +’b’ -> Result: Error converting data type varchar to numeric.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.