Export (0) Print
Expand All
5 out of 8 rated this helpful - Rate this topic

SET CONCAT_NULL_YIELDS_NULL

SQL Server 2000

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

Syntax

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

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

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 manipulating indexes on computed columns or indexed views. If SET CONCAT_NULL_YIELDS_NULL is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see Considerations When Using SET Statements in SET.

See Also

SET

Setting Database Options

sp_dboption

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.