Friday, August 24, 2018

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.

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

No comments:

Post a Comment