QUOTED_IDENTIFIER And PERSISTED Computed Columns

Many of us are familiar with this error message:

INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. 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.

What is missing in the list however is PERSISTED Computed Columns. Below are two examples with the only difference being whether the computed column is persisted.

Example 1 - Creates a table with a non-persisted computed column. QUOTED_IDENTIFIER is ON for table create.

But we are able to INSERT (and SELECT) data with QUOTED_IDENTIFIER OFF.

DROP TABLE IF EXISTS [dbo].[Numbers];

GO

SET QUOTED_IDENTIFIER ON;

GO

CREATE TABLE [dbo].[Numbers](

       [ID] [int] NOT NULL,

       [Number1] [int] NOT NULL,

       [Number2] [int] NOT NULL,

       [SumOfNumbers]  AS ([Number1]+[Number2]),

 CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED

(

       [ID] ASC

));

GO

SET QUOTED_IDENTIFIER OFF;

GO

INSERT

INTO dbo.Numbers (ID,Number1,Number2)

VALUES (1,5,6);

SELECT * FROM dbo.Numbers;

Results...

Example 2 - Creates a table with a persisted computed column. QUOTED_IDENTIFIER is ON for table create.

But we are not able to INSERT data with QUOTED_IDENTIFIER OFF.

DROP TABLE IF EXISTS [dbo].[Numbers] ;

GO

SET QUOTED_IDENTIFIER ON;

GO

CREATE TABLE [dbo].[Numbers](

       [ID] [int] NOT NULL,

       [Number1] [int] NOT NULL,

       [Number2] [int] NOT NULL,

       [SumOfNumbers]  AS ([Number1]+[Number2]) PERSISTED,

 CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED

(

       [ID] ASC

));

GO

SET QUOTED_IDENTIFIER OFF;

GO

INSERT

INTO dbo.Numbers (ID,Number1,Number2)

VALUES (1,5,6);

SELECT * FROM dbo.Numbers; 

Results...

INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. 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.

...with no mention of persisted computed columns. It mentions indexed computed columns and persistence is a prerequisite to index a computed column. But this example only persisted the computed column and did not index it. 

If you change  QUOTED_IDENTIFIER to ON, it works even when the computed column is persisted. 

I found some old documentation here, but nothing current. 

Anyway, it took me some time to figure it out and so I decided to post.

Later...