‘Incorrect SET Options’ Error When Building Database Project

by

According to BOL:

Indexed views and indexes on computed columns store results in the
database for later reference. The stored results are valid only if all
connections referring to the indexed view or indexed computed column
can generate the same result set as the connection that created the
index.

In order to create a table with a persisted, computed column, the following connection settings must be enabled: 

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT ON
SET QUOTED_IDENTIFIER ON

These values are set on the database level and can be viewed using: 

SELECT 
    is_ansi_nulls_on,
    is_ansi_padding_on,
    is_ansi_warnings_on,
    is_arithabort_on,
    is_concat_null_yields_null_on,
    is_numeric_roundabort_on,
    is_quoted_identifier_on
FROM sys.databases

However, the SET options can also be set by the client application connecting to SQL Server.

A perfect example is SQL Server Management Studio which has the default values for SET ANSI_NULLS and SET QUOTED_IDENTIFIER both to ON. This is one of the reasons why I could not initially duplicate the error you posted.

Anyway, to duplicate the error, try this (this will override the SSMS default settings): 

SET ANSI_NULLS ON
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON 
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE T1 (
    ID INT NOT NULL,
    TypeVal AS ((1)) PERSISTED NOT NULL
)

You can fix the test case above by using: 

SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

I would recommend tweaking these two settings in your script before the creation of the table and related indexes.

More Related Contents:

  1. If condition in SQL Server 2008
  2. Using merge..output to get mapping between source.id and target.id
  3. SQL Server, How to set auto increment after creating a table without data loss?
  4. How can I truncate a datetime in SQL Server?
  5. Errors: “INSERT EXEC statement cannot be nested.” and “Cannot use the ROLLBACK statement within an INSERT-EXEC statement.” How to solve this?
  6. How to add Active Directory user group as login in SQL Server
  7. Why use the INCLUDE clause when creating an index?
  8. How to set a default value for an existing column
  9. An attempt was made to access a socket in a way forbidden by its access permissions
  10. SQL Server: combining multiple rows into one row
  11. SQL Server Query: Fast with Literal but Slow with Variable
  12. How do I find a stored procedure containing ?
  13. Should every User Table have a Clustered Index?
  14. An attempt to login using SQL authentication failed
  15. How to set monday as first day of week in SQL Server
  16. SQL Server, the misleading XLOCK & optimizations
  17. Drop primary key using script in SQL Server database [duplicate]
  18. How to find SQL Server running port?
  19. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  20. Changing SQL Server collation to case insensitive from case sensitive?
  21. Unable to login to SQL Server + SQL Server Authentication + Error: 18456
  22. Database design: Calculating the Account Balance
  23. The request failed or the service did not respond in a timely fashion?
  24. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  25. INFORMATION_SCHEMA vs sysobjects
  26. Merge multiple rows into a single row
  27. In SQL Server, how can I find everywhere a column is referenced?
  28. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  29. Why insert-select to variable table from XML variable so slow?
  30. Generate MD5 hash string with T-SQL

Leave a Comment