SQL bit by bit: The Role of the SET QUOTED_IDENTIFIER OFF statement

Editor's note:SET QUOTED_IDENTIFIER

Make Microsoft®SQL Server™Follow the SQL-92 rules for the quotation mark separator and text string. The identifiers separated by double quotation marks can be keywords reserved by Transact-SQL, or can contain characters that are not allowed by the syntax rules of Transact-SQL identifiers.

First look at the following SQL statements

 
 

  
  
SET QUOTED_IDENTIFIER ON 
  
  
SELECT * FROM "USER"    WHERE a='netasp'   
  
  
   
  
  
SET QUOTED_IDENTIFIER ON 
  
  
SELECT * FROM [USER] WHERE a='netasp'   
  
  
 
  
  
SET QUOTED_IDENTIFIER OFF 
  
  
SELECT * FROM [USER]    WHERE a="netasp"   
  
  
 
  
  
SET QUOTED_IDENTIFIER OFF 
  
  
SELECT * FROM [USER]    WHERE a= 'netasp' 
 
 

When you create a table named "USER" in the database, it is often troublesome because the USER is a keyword in SQL, but the preceding statements do not report an error. Another concept: the identifier is [] in SQL.

When the SET QUOTED_IDENTIFIER value is ON, the characters in double quotation marks are treated as database objects. That is to say, double quotation marks "" and identifiers [] have the same effect. They all indicate that the referenced characters are database objects. Single quotation marks (') indicate the boundary of a string.

When SET QUOTDE_IDENTIFIER OFF, double quotation marks are interpreted as string boundary, which is similar to single quotation marks. Double quotation marks cannot be used as identifiers, but can be used as character boundary. They have the same effect as single quotation marks.

A summary can be made: When SET QUOTED_IDENTIFIER ON "" is equivalent to [], it indicates the database object; when SET QUOTED_IDENTIFIER OFF "" is equivalent to '', it indicates the string boundary; the double quotation marks here are not combined by two single quotation marks. They are produced by shift +. Beginners may make such mistakes.