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.
From SQL Server 2012 (11.0.7001)
To SQL Server 2017 CU 1 (14.0.1000)
Windows Server 2012 R2
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.
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;
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;
A simple approach to resolve this was to set it to 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.
When tested with the job on a different server :11.0.7001.0
On yet another server: 14.0.3015.40
After playing around with the connections at server and database level, I found what caused this.
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).
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.