Quoted_Identifiers

Recently came across some problems that were being caused by quoted_identifiers being turned off.

Here is what turning it on or off allows you to do:

Two simple queries that are allowed at all times:

select top 100 * from tmpTable
--following 2 queries are not affected by quoted identifiers being turned on or off
--[] and ‘’ are always valid delimiters ([] for identifiers and ‘’ for literals)
select top 100 * from [tmpTable] 
select 'hello world'

set quoted_identifier off

select top 100 * from [tmpTable]
--select top 100 * from "tmpTable" –“” is not allowed as quoted identifiers is off
--as quoted identifiers is off – literals can be delimited by double or single quotes
select "hello world"
select 'hello world'

set quoted_identifier on

select top 100 * from [tmpTable]
select top 100 * from "tmpTable" –“” is allowed for identifiers as quoted identifiers is on
--select "hello world" –“” as a delimiter for literals is not allowed as quoted identifiers is on
select 'hello world'

And the problem I was facing was caused by the following requirement on Quoted Identifiers (from MSDN): SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

And here is a query to find all stored procs with quoted identfiers turned off (via zvolkov.com).

SELECT
SCHEMA_NAME(s.schema_id)  + '.' + s.name AS name,
s.create_date,
s.modify_date,
OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects s
WHERE
s.type IN ('P','TR','V','IF','FN','TF')
AND OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') = 0
ORDER BY SCHEMA_NAME(s.schema_id)  + '.' + s.name