top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

What does it mean to have QUOTED_IDENTIFIER ON?

+4 votes
77 views

What are the implications of having it OFF?

posted Feb 20, 2014 by Muskan

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

When SET QUOTED_IDENTIFIER is OFF (default), literal strings in expressions can be delimited by single or double quotation marks.
When SET QUOTED_IDENTIFIER is ON, 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.
SET QUOTED_IDENTIFIER must be ON when creating or manipulating 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.
The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting.
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure. When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.
SET QUOTED_IDENTIFIER OFF
GO
-- Attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" int IDENTITY, "order" int)
GO

SET QUOTED_IDENTIFIER ON
GO
-- Will succeed.
CREATE TABLE "select" ("identity" int IDENTITY, "order" int)
GO
- See more at: http://www.debuggersden.com/questions/what-does-it-mean-to-have-quoted_identifier-on-what-are-the-implications-of-having-it-off/#sthash.x0zGNZkk.dpuf

answer Feb 21, 2014 by Neeraj Pandey
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...