Quoted Identifiers in SQL Server 2000

By Brent Huscher on 11 December 2000 | Tags: Queries

Quoted identifiers are used by default in SQL Server 2000, that is, they are set to ON. This is different from SQL Server 7.0 where they were set to OFF by default. Read on to see what this means for you.

Quoted identifiers are used by default in SQL Server 2000, that is, they are set to ON. This is different from SQL Server 7.0 where they were set to OFF by default.

This bit me in a stored proc containing the following code that executes a prepared SQL statement. This works fine in SQL Server 7.0 with the default of quoted identifiers 'OFF' by default but breaks when implemented this way in SQL Server 2000 with the new default of quoted identifiers 'ON':

Example 1: (the SQL code)

  SET @mycommand = 'BACKUP DATABASE ' + 
    @db_name + ' TO ' + @dump_device_name + ' WITH INIT, 
    NAME = "' + @db_name + '"'  EXEC (@mycommand)

Example 2: (this shows a value that gets passed into the @mycommand variable)

  BACKUP DATABASE master TO SPYDER01_DUMP WITH INIT, NAME = "master"

The variable @db_name is being used in two different ways in this statement; first, as a literal and second as a string value. You can get away with this when the quoted identifiers are turned OFF but not when they are turned ON. The error message that you will get when you execute the statement with
quoted identifiers are turned ON is:

  Line 1: Incorrect syntax near 'master'.

Example 3: (my workaround for servers that have quoted identifiers turned
ON)

  SET @mycommand = 'BACKUP DATABASE ' + @db_name + 
    ' TO ' + @dump_device_name + ' WITH INIT, 
    NAME = ' + CHAR(39) + @db_name + CHAR(39)
  EXEC (@mycommand)

Example 4: (this shows a value that gets passed into the @mycommand variable using the revised code)

  BACKUP DATABASE master TO SPYDER01_DUMP WITH INIT, NAME = 'master'

My workaround is a little unorthodox -- it uses CHAR(39), which is the ASCII value for the single-quote (') character. You could also toggle the quoted identifiers setting off and on using a pair of SET QUOTED_IDENTIFIER OFF / SET QUOTED_IDENTIFIER ON statements.

The moral of the story: Be sure to test any prepared SQL code that you have in SQL Server 7.0 has embedded double-quotes before assuming it will work on SQL Server 2000 with the new quoted identifiers ON by default.

Here is the official word about quoted identifiers from the SQL Server 2000 Books Online:

The QUOTED_IDENTIFIER setting determines what meaning Microsoft SQL Server gives to double quotation marks ("). When QUOTED_IDENTIFIER is set to OFF, double quotation marks delimit a character string, just as single quotation marks do. When QUOTED_IDENTIFIER is set to ON, double quotation marks delimit an identifier, such as a column name. An identifier must be enclosed in double quotation marks; for example, if its name contains characters that are otherwise not allowed in an identifier, including spaces and punctuation, or if the name conflicts with a reserved word in Transact-SQL. Regardless of the QUOTED_IDENTIFIER setting, an identifier can also be delimited by square brackets.

The meaning of the following statement, for example, depends on whether QUOTED_IDENTIFIER is set to ON or OFF:

SELECT "x" FROM T

If QUOTED_IDENTIFIER is set to ON, "x" is interpreted to mean the column named x. If it is set to OFF, "x" is the constant string x and is equivalent to the letter x.

If the previous SELECT statement example were part of a stored procedure created when QUOTED_IDENTIFIER was set to ON, then "x" would always mean the column named x. Even if the QUOTED_IDENTIFIER setting was later switched, and set to OFF, the stored procedure would respond as if it were set to ON and treat "x" as the column named x.

When the SQL Server Upgrade Wizard re-creates database objects in SQL Server 2000, the QUOTED_IDENTIFIER setting determines how all of these objects behave. If all database objects were created in SQL Server 6.5 with the same QUOTED_IDENTIFIER setting, click that setting, either On or Off. If objects were created in SQL Server version 6.5 with a mix of the two settings, or if
you are unsure of the settings used, click Mixed.

With the Mixed option, the SQL Server Upgrade Wizard first converts all objects containing double quotation marks with QUOTED_IDENTIFIER set ON. The SQL Server Upgrade Wizard then converts any objects that failed to be created with QUOTED_IDENTIFIER set OFF.