CONCAT_NULL_YIELDS_NULL and CONCAT: How NULL Behaves in Strings

The CONCAT_NULL_YIELDS_NULL setting decides what the plus operator does with NULL, and the CONCAT function ignores it. With the setting ON, a string plus NULL gives NULL. With it OFF, the string survives. CONCAT keeps the string either way.

The Setting and the Function Side by Side

Run the same two expressions twice, first with the setting ON and then with it OFF. The script creates a database named ConcatNullDemo, so run it on a test server. It switches the setting back ON at the end, because the setting belongs to the connection.

IF DB_ID(N'ConcatNullDemo') IS NULL CREATE DATABASE ConcatNullDemo;
GO
USE ConcatNullDemo;
GO
SELECT 'Some Value' + NULL AS PlusOperator, CONCAT('Some Value', NULL) AS ConcatFunction;
GO
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'Some Value' + NULL AS PlusOperator, CONCAT('Some Value', NULL) AS ConcatFunction;
GO
SET CONCAT_NULL_YIELDS_NULL ON;

The plus operator follows the CONCAT_NULL_YIELDS_NULL setting. The CONCAT function treats every NULL as an empty string and never checks the setting. The rule comes from the meaning of NULL. A missing value plus text is still a missing value, so the plus operator returns NULL.

CONCAT has a second advantage. It turns numbers and dates into text on its own, so it needs no CAST.

SELECT CONCAT('Order ', 42, ' on ', CAST('2026-10-06' AS date)) AS Label;

The plus operator behaves differently. 'Order ' + 42 fails with Msg 245, because SQL Server tries to turn the text into a number. A CAST of the number to text fixes it.

Which value applies to you? Management Studio and the common drivers connect with the setting ON. The database also stores an option named CONCAT_NULL_YIELDS_NULL, and for a new database it is 0. The connection wins, as the next query shows for a sqlcmd session.

SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS SessionSetting,
       d.is_concat_null_yields_null_on AS DatabaseOption
FROM sys.databases AS d
WHERE d.name = DB_NAME();

A Real Query With Middle Names

The setting matters when a column can be NULL. A middle name is the classic case. The table below has two customers, and one has no middle name.

DROP TABLE IF EXISTS dbo.Customer;
CREATE TABLE dbo.Customer (CustomerID int NOT NULL, FirstName nvarchar(30) NOT NULL, MiddleName nvarchar(30) NULL, LastName nvarchar(30) NOT NULL);
INSERT dbo.Customer VALUES (1, N'Maya', N'Rose', N'Collins'), (2, N'Leo', NULL, N'Brennan');
GO
SELECT CustomerID,
       FirstName + N' ' + MiddleName + N' ' + LastName AS PlusName,
       CONCAT(FirstName, N' ', MiddleName, N' ', LastName) AS ConcatName,
       CONCAT_WS(N' ', FirstName, MiddleName, LastName) AS WsName,
       FirstName + COALESCE(N' ' + MiddleName, N'') + N' ' + LastName AS SafePlusName
FROM dbo.Customer;

The plus operator loses the whole name for Leo. CONCAT keeps it but leaves two spaces where the middle name should be. The table shows them as non-breaking spaces so you can see the gap. CONCAT_WS, which needs SQL Server 2017, skips the NULL together with its separator, so the result is clean. The last column works on every version. It adds the space and the middle name as one unit. That unit becomes NULL when the middle name is NULL.

Why CONCAT_NULL_YIELDS_NULL Should Stay ON

Some old code turns the setting OFF to make a plus behave like CONCAT. Don’t copy it. The first reason is that Microsoft documents the option as always ON in a future version. SQL Server 2025 still accepts the OFF statement, and it records the use in a deprecated feature counter. Read the counter before and after to see it.

SELECT RTRIM(instance_name) AS Feature, cntr_value AS TimesUsed
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%Deprecated Features%' AND instance_name LIKE N'SET CONCAT_NULL_YIELDS_NULL OFF%';
GO
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT RTRIM(instance_name) AS Feature, cntr_value AS TimesUsed
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%Deprecated Features%' AND instance_name LIKE N'SET CONCAT_NULL_YIELDS_NULL OFF%';

The second reading is one higher than the first, or more if another session used the OFF setting in between. The second reason is stronger. A persisted computed column needs the setting ON. So do an index on one and an indexed view. With OFF, the change fails.

SET CONCAT_NULL_YIELDS_NULL OFF;
GO
ALTER TABLE dbo.Customer ADD FullName AS FirstName + N' ' + MiddleName + N' ' + LastName PERSISTED;
GO
SET CONCAT_NULL_YIELDS_NULL ON;
GO
ALTER TABLE dbo.Customer ADD FullName AS FirstName + N' ' + MiddleName + N' ' + LastName PERSISTED;

The first ALTER TABLE fails with Msg 1934, and the message names the setting. The line number in the message depends on where the statement sits in your query window. After the setting goes back ON, the same statement works. The persisted column holds NULL for Leo, because the plus operator is back to its normal rule.

The third reason is plain. The same query now gives different answers on different connections. A report that runs fine in Management Studio can change when a legacy application sets the option OFF.

NULL in Numbers Has No Such Setting

Some people look for a similar setting for numbers. It doesn’t exist. Any arithmetic with NULL returns NULL, and SET ANSI_WARNINGS OFF, a setting some people suggest, doesn’t change that. The fix goes into the query. SUM ignores NULL values, but a SUM over a column that holds only NULL returns NULL. Adding that result to another number returns NULL too.

DROP TABLE IF EXISTS dbo.Amounts;
CREATE TABLE dbo.Amounts (col1 int NULL, col2 int NULL);
INSERT dbo.Amounts VALUES (100, NULL), (NULL, NULL);
GO
SELECT SUM(col1) AS Sum1,
       SUM(col2) AS Sum2,
       SUM(col1) + SUM(col2) AS Added,
       COALESCE(SUM(col1), 0) + COALESCE(SUM(col2), 0) AS AddedSafe
FROM dbo.Amounts;

COALESCE replaces a NULL with a value you choose. Put it around each part, not around the finished sum, or you’ll hide the NULL you didn’t expect.

You could argue that a legacy application needs the OFF setting, and rewriting every plus is expensive. That’s a fair point for a database you can’t change. Even then, set the option in the connection of that one application, and never at the database level. Plan the rewrite with COALESCE or CONCAT_WS, so the day the option is removed isn’t a surprise.

What to Remember

Treat CONCAT_NULL_YIELDS_NULL as ON and write code that doesn’t depend on it. Use CONCAT_WS for names and lists. Use COALESCE around a part when you need the plus operator. Use CONCAT when an empty string is the right stand-in for NULL.

When you finish, run the cleanup script. It drops the demo database.

USE master;
GO
ALTER DATABASE ConcatNullDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ConcatNullDemo;

A NULL is not an empty string, it is a missing answer, and CONCAT quietly decides for you.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.