Create table error: Concat Null Yields Null

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 http://msdn.microsoft.com/en-us/library/ms176056.aspx

Advertisements