Understanding QUOTED_IDENTIFIER

Question

We just ran into a problem with one of our stored procs throwing an error;

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

I fixed it by modifiying the stored proc and setting the quoted identifier to ON. The thing is, I did this prior to the CREATE PROCEDURE call. For example;

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertStuff]

I would have thought that this affected the CREATE PROCEDURE statement, but wouldn't have affected anything to do with the execution of that procedure.

Our scripts are all deployed as drop and create scripts and run via sqlcmd. I've just read that here (search for Example: Executing SQLCMD) and here that sqlcmd executes with quoted identifier off. I've changed our script to include the -I switch to see if that fixes our issues.

My questions are then;

1) Does the SET QUOTED_IDENTIFIER ON statement affect only the DDL CREATE PROCEDURE statement, or does it also affect the execution of the stored proc also? My quick test indicates the latter.

2) As the default for this switch is ON, I am presuming that by me setting the -I switch of my sqlcmd query will have no adverse affects. For all intents and purposes, I will assume it is the same as copying the contents of the script and then pasting them into query manager and hitting execute. Please correct me if I am wrong about this. Our simple deploy script is as follows;

@echo off

SET dbodir=../Schema Objects/Schemas/dbo/Programmability/Stored Procedures/
SET tpmdir=../Schema Objects/Schemas/TPM/Programmability/Stored Procedures/

echo --- Starting dbo schema

for %%f in ("%dbodir%*.sql") do (echo Running %%f.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -i "%dbodir%%%f")

echo --- Completed dbo schema

echo --- Starting TPM schema

for %%g in ("%tpmdir%*.sql") do (echo Running %%g.... && @sqlcmd -I -U %1 -P %2 -S %3 -d %4 -i "%tpmdir%%%g")

echo --- Completed TPM schema

pause

Thanks in advance

Edit:

It seems as though there is some further info to determine where the SET options for stored procs are stored, and the accepted answer to this provides some details on general rules regarding generic order of precedence that applies to the SET options. The comments on this also state that;

" ...Only QUOTED_IDENTIFER and ANSI_NULLS settings are captured at procedure creation time." "...SET QUOTED IDENTIFIER can not be set at run time inside the stored proc" (my emphasis).

I feel that answers my first question.

Any takers for the second part?

Answer 1

Looking for an understanding of QUOTED_IDENTIFIER i will post some understanding here.

Short version

ANSI demanded that quotation marks be used around identifiers (not around strings). SQL Server supported both:

SQL Server originally:

• SELECT "Hello, world!" --quotation mark
• SELECT 'Hello, world!' --apostrophe
• CREATE TABLE [The world's most awful table name] ([Hello, world!] int)
• SELECT [Hello, world!] FROM [The world's most awful table name]

ANSI (i.e. SET QUOTED_IDENTIFIER ON):

• SELECT "Hello, world!" --quotation mark no longer valid in ANSI around strings
• SELECT 'Hello, world!' --apostrophe
• CREATE TABLE "The world's most awful table name" ("Hello, world!" int)
• SELECT "Hello, world!" FROM "The world's most awful table name"

Long Version

Originally, SQL Server allowed you to use quotation marks ("...") and apostrophes ('...') around strings interchangeably (like Javascript does):

• SELECT "Hello, world!" --quotation mark
• SELECT 'Hello, world!' --apostrophe

And if you wanted a name table, view, procedure, column etc with something that would otherwise violate all the rules of naming objects, you could wrap it in square brackets ([, ]):

CREATE TABLE [The world's most awful table name] ([Hello, world!] int)
SELECT [Hello, world!] FROM [The world's most awful table name]

And that all worked, and made sense.

Then came ANSI

Then ANSI came along and had other ideas:

• if you have a funky name, wrap it in quotation marks ("...")
• use apostrophe ('...') for strings
• and we don't even care about your square brackets

Which means that if you wanted to "quote" a funky column or table name you must use quotation marks:

SELECT "Hello, world!" FROM "The world's most awful table name"

If you knew SQL Server, you knew that quotation marks were already being used to represent strings. If you blindly tried to execute that ANSI-SQL as though it were T-SQL: it's nonsense, and SQL Server told you so:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'The world's most awful table name'.

It is the moral equivalent of trying to execute:

SELECT 'Hello, world!' FROM 'The world''s most awful table name'

Which is like executing:

SELECT 'string' FROM 'string'

You must opt-in to the new ANSI behavior

So Microsoft added a feature to let you opt-in to the ANSI flavor of SQL.

Original (or QUOTED_IDENTIFIER off):

SELECT "Hello, world!" --valid
SELECT 'Hello, world!' --valid

SET QUOTED_IDENTIFIER ON:

SELECT "Hello, world!" --INVALID
SELECT 'Hello, world!' --valid

SQL Server still lets you use [square brackets], rather than forcing you to use "quotation marks". But with QUOTED_IDENTIFIER ON, you cannot use "double quote quotation mark around strings", you must only use 'the single quote apostrophe'.

Answer 2

I saved the following command to a textfile, then executed it with SQLCMD:

SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER OFF

Checking in SQL profiler, SQLCMD -i <filename> connects with the following connection options on my system:

-- network protocol: LPC
set quoted_identifier on
...

however the following command issued by SQLCMD when it connects:

SET QUOTED_IDENTIFIER OFF SET TEXTSIZE 4096

and then it runs my script.

So, the answer to 2) is no - running a script with SQLCMD -i is not the same as executing from SSMS (with the default connections options). If a script requires QUOTED_IDENTIFIER ON, then you need to explicitly set it at the start if you're going to execute it this way.

Answer 3

About your question #1, the reason is stated in Considerations When You Use the SET Statements. It states:

Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.

Answer 4

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO
--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

ANSI NULL ON/OFF:

This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

QUOTED IDENTIFIER ON/OFF:

This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.