Using Delimited Identifiers

Consideration for using delimited identifiers.

  • You can use delimited identifiers for table names, column names, and view names. You cannot use delimited identifiers for other object names.

  • If you choose to use delimited identifiers, use set quoted_identifier on, and drop and re-create all the procedures, triggers, and views that use the identifier. Edit the text for those objects, enclosing the reserved words in double quotes and enclosing all character strings in single quotes.

    The following example shows the changes to make to queries in order to use delimited identifiers. This example updates a table named work, with columns named key and level. Here is the pre-release 10.0 query, which encloses character literals in double quotes, and the edited version of the query for use with delimited identifiers:
    /* pre-release 10.0 version of query */
    update work set level = "novice"
        where key = "19-732"
    /* 10.0 or later version of query, using 
    ** the quoted identifier option
    */
    update "work" set "level" = 'novice'
        where "key" = '19-732'
  • All applications that use the reserved word as an identifier must be changed as follows:
    • The application must set the quoted identifier option on.

    • All uses of the reserved word must be enclosed in double quotes.

    • All character literals used by the application while the quoted identifier option is turned on must be enclosed in single quotes. Otherwise, the SAP ASE server attempts to interpret them as object names.

    For example, the following query results in an error message:
    set quoted_identifier on
    select * from titles where title_id like "BU%"
    Here is the correct query:
    select * from titles where title_id like ’BU%’
  • Stored procedures that you create while the delimited identifiers are in effect can be run without turning on the option. (The allow updates to system tables option also works this way.) This means that you can turn on quoted identifier mode, drop a stored procedure, edit it to insert quotation marks around reserved words used as identifiers, and re-create the procedure. All users can execute the procedure without using set quoted_identifier.