kohera-logo-regular.svg

When the SQL Server Agent overrides default behavior: QUOTED_IDENTIFIER

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 3 hours 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 for automation purposes, issues arose:

When I checked the captured errors in a log table used in the queries, 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.”

From Microsoft docs:

“When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object 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

I know, It was my mistake to put the query directly into the job step, but it was a test run, I and wanted to be quick.

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

QUOTEDIDENTIFIER
OFF

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

 

QUOTEDIDENTIFIER
ON

 

More Testing

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  a job on a different server :11.0.7001.0

 

QUOTEDIDENTIFIER
OFF

 

On yet another server: 14.0.3015.40

 

QUOTEDIDENTIFIER
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 behaviour, 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

Conclusion

The main thing to keep in mind, is that when adapting indexes in a t-sql job, (without using a stored procedure), your filtered index creation will fail. Your strings might also get interpreted differently.  Turning this on by using SET QUOTED_IDENTIFIER ON in your job step will fix this.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...