Procedures shipped with SSISDB causing issues with Filtered Indexes

Question

SQL Server 2014 I believe. Procedures in SSISDB have QUOTED_IDENTIFIER set to OFF for some reason, and was wondering if anyone had any frame of reference as to why this might be, and if it might be important.

Unfortunately I don't know if changing these procedures to set these to ON would be advisable, but if anyone had any ideas about that, it would also be greatly appreciated.

Answer 1

Best MSSQL practice is to set it OFF.

It will basically allow you to use reserved keywords or can contain characters not generally allowed by the Transact-SQL. i.e "select", "from", "identity" etc..

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

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers.

Usage of QUOTED_IDENTIFIER is ON

• when you are creating a filtered index
• when you invoke XML data type methods
• JSON source data import in database

Permission required to change the settings

• User requires membership in the public role

Example

SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);

↪To use "select" as table name, above query will get failed when QUOTED_IDENTIFIER set OFF

SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);

↪To use "select" as table name, above query will get succeed when QUOTED_IDENTIFIER set ON

Reference