Friday 1 July 2011

SET QUOTED_IDENTIFIER ON/OFF

When this is set to 'ON', any string enclosed with double quotes ( “ ) is treated as a T-SQL Identifier (such as table name, procedure name or column name) and the T-SQL rules for naming identifiers will not apply to it. To define a normal string literal, enclose it with single quotes ( ' ).

When this is set to 'OFF', any string enclosed with either single quotes or double quotes will be treated as a literal.

The default behavior is 'ON' in any database.

Example:
SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SELECT "identity","order" 
FROM "select"
ORDER BY "order";
GO

Reference:
http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/

Further reference:
http://msdn.microsoft.com/en-us/library/ms174393.aspx

No comments: