Skip to content

Latest commit

 

History

History
95 lines (71 loc) · 4.33 KB

set-concat-null-yields-null-transact-sql.md

File metadata and controls

95 lines (71 loc) · 4.33 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
WilliamDAssafMSFT
wiassaf
04/04/2024
sql
t-sql
reference
CONCAT_NULL_YIELDS_NULL_TSQL
SET CONCAT_NULL_YIELDS_NULL
CONCAT_NULL_YIELDS_NULL
SET_CONCAT_NULL_YIELDS_NULL_TSQL
CONCAT_NULL_YIELDS_NULL option
null values [SQL Server], concatenation results
concatenation [SQL Server]
SET CONCAT_NULL_YIELDS_NULL statement
TSQL
>=aps-pdw-2016||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)

[!INCLUDE sql-asdbmi-asa-pdw-fabricse-fabricdw]

Controls whether concatenation results are treated as null or empty string values.

Note

SET CONCAT_NULL_YIELDS_NULL OFF and the CONCAT_NULL_YIELDS_NULL OFF database option are deprecated. Starting with [!INCLUDE _ss2017], CONCAT_NULL_YIELDS_NULL is always set to ON. Deprecated features shouldn't be used in new applications. For more information, see Deprecated Database Engine features in SQL Server 2017.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

Syntax for [!INCLUDE ssnoversion-md.md], [!INCLUDE sssodfull-md.md], [!INCLUDE fabric]

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

Syntax for [!INCLUDE ssazuresynapse-md.md] and [!INCLUDE sspdw-md.md]

SET CONCAT_NULL_YIELDS_NULL ON

Remarks

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

If SET CONCAT_NULL_YIELDS_NULL is not specified, the setting of the CONCAT_NULL_YIELDS_NULL database option applies.

Note

SET CONCAT_NULL_YIELDS_NULL is the same setting as the CONCAT_NULL_YIELDS_NULL setting of ALTER DATABASE.

The setting of SET CONCAT_NULL_YIELDS_NULL is set at execute or run time and not at parse time.

SET CONCAT_NULL_YIELDS_NULL must be ON when creating or altering indexed views, indexes on computed columns, filtered indexes or spatial indexes. If SET CONCAT_NULL_YIELDS_NULL is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns, filtered indexes, spatial indexes or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

When CONCAT_NULL_YIELDS_NULL is set to OFF, string concatenation across server boundaries cannot occur.

To view the current setting for this setting, run the following query.

DECLARE @CONCAT_SETTING VARCHAR(3) = 'OFF';  
IF ( (4096 & @@OPTIONS) = 4096 ) SET @CONCAT_SETTING = 'ON';  
SELECT @CONCAT_SETTING AS CONCAT_NULL_YIELDS_NULL;

Examples

The following example showing using both SET CONCAT_NULL_YIELDS_NULL settings.

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';  
GO  
-- SET CONCAT_NULL_YIELDS_NULL ON and testing.  
SET CONCAT_NULL_YIELDS_NULL ON;  
GO  
SELECT 'abc' + NULL ;  
GO  
  
-- SET CONCAT_NULL_YIELDS_NULL OFF and testing.  
SET CONCAT_NULL_YIELDS_NULL OFF;  
GO  
SELECT 'abc' + NULL;   
GO

Related content