Data Warehousing and Data Science

20 May 2011

Create table error: Concat Null Yields Null

Filed under: SQL Server — Vincent Rainardi @ 10:45 pm

Today I was trying to create a table and got this message:

SQLServer1: DDL_DATABASE_LEVEL_EVENTS: Error: INSERT failed because the following SET options have incorrect settings: ‘CONCAT_NULL_YIELDS_NULL’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 3616, Level 16, State 2, Line 1
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

The DDL was:

create table schema1.table1
( column1 int not null,
  column2 varchar(10),
  column3 varchar(20),
  constraint PK_table1 primary key clustered
  ( column1 )

Solution: Tools menu, option, Query Execution, SQL Server, Advanced: make sure “SET CONCAT_NULL_YIELDS_NULL” is checked.

If it is not checked, the create index statement will error. In the above DDL it tried to create a clustered index for the PK hence it failed.

For further info see

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: