Intro

For a customer, I recently migrated one of their databases from 2012 to 2017, whilst putting compression on all the indexes.

The process of recreating the Indexes on 8 new filegroups took ~3hours for a 400GB database.

Specs

From SQL Server 2012 (11.0.7001)

To SQL Server 2017 CU 1 (14.0.1000)

Windows Server 2012 R2

 

Query execution

When manually executing the queries, life was bliss.

When putting the queries needed to complete the task in a job with a few steps for automation purposes, the job completed since there was try – catch logic present.

When I checked the errors captured I was greeted with this message, which was not present when I manually ran the queries:

“CREATE INDEX 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 the QUOTED_IDENTIFIER setting?

Setting this on means that characters enclosed in double quotes, “ “ are treated as identifiers instead of literals. The default for this is ON, and this is a general better practice.

From Microsoft docs:

“When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers…”

“When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. “

“SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.” https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql

Since I had filtered indexes, QUOTED_IDENTIFIER needed to be ON at all times when executing the query’s.

 

Testing

If I execute the following statement under my account (sysadmin) on the database to be compressed

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';

IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';

SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;
QUOTED_IDENTIFIER
ON

 

When I put the query to check the quoted identifier in a job and run it, (in the user database to be compressed) this happens:

 

create table dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER varchar(3)) 
DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) = 256 ) 
SET @QUOTED_IDENTIFIER = 'ON'; SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER; 
insert into dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER) select @QUOTED_IDENTIFIER

 


select * from dbo.QUOTEDIDENTIFIER_CHECK;


QUOTED_IDENTIFIER
OFF

 

A simple approach to resolve this was to set it to ON.

 

QUOTED_IDENTIFIER
ON

 

And that is what I did, added SET QUOTED_IDENTIFIER ON; to the executed statements.

But, I also wanted to know what causes this.

 

More testing

When tested with the job on a different server :11.0.7001.0

QUOTED_IDENTIFIER
OFF

On yet another server: 14.0.3015.40

QUOTED_IDENTIFIER
OFF

 

After playing around with the connections at server and database level, I found what caused this.

 

The Culprit

 

When checking the profiler when executing the job, I found that it gets explicitly set to off before the step execution. Under the SQLSERVERAGENT service account (also not best practice but it illustrates the point better).

Closer look:

Executed by the agent:

Seems that this is default behavior for the Agent service. For now, I have not found a better way than explicitly setting quoted identifier on .

The reason behind this behavior, is something that I am still looking for, since it is not documented in the Microsoft docs on quoted identifier.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql

 

Leave a Comment

Your email address will not be published. Required fields are marked *