Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

Naming conventions

Naming conventions

The characters recognized by Adaptive Server are limited in part by the language of the installation and the default character set. Therefore, the characters allowed in SQL statements and in the data contained in the server vary from installation to installation and are determined in part by definitions in the default character set.

SQL statements must follow precise syntactical and structural rules, and can contain operators, constants, SQL keywords, special characters, and identifiers. Identifiers are objects within the server, such as database names or table names. Naming conventions vary for some parts of the SQL statement. Operator, constants, SQL keywords, and Transact-SQL extensions must adhere to stricter naming restrictions than identifiers, which themselves cannot contain operators and special characters. However, the data contained within the server can be named following more permissive rules. Figure 1-2 illustrates these relationships.

The sections that follow describe the sets of characters that can be used for each part of a statement. The section on identifiers also describes naming conventions for database objects.

SQL data characters

The set of SQL data characters is the larger set from which both SQL language characters and identifier characters are taken. Any character in Adaptive Server's character set, including both single-byte and multibyte characters, can be used for data values.

SQL language characters

SQL keywords, Transact-SQL extensions, and special characters such as the comparison operators > and <, can be represented only by 7-bit ASCII values A- Z, a -z, 0-9, and the following ASCII characters:

Identifiers

Conventions for naming database objects apply throughout Adaptive Server software and documentation. Identifiers can be as many as 30 bytes in length, whether or not multibyte characters are used. The first character of an identifier must be declared as an alphabetic character in the character set definition in use on Adaptive Server.

The @ sign or _ (underscore character) can also be used. The @ sign as the first character of an identifier indicates a local variable.

Temporary table names must either begin with # (the pound sign) if they are created outside tempdb or be preceded by "tempdb.". Table names for temporary tables that exist outside tempdb should not exceed 13 bytes in length, including the number sign, since Adaptive Server gives them an internal numeric suffix.

After the first character, identifiers can include characters declared as alphabetic, numeric, or the character $, #, @, _, ¥ (yen), or £ (pound sterling). However, you cannot use two @@ symbols together at the beginning of a named object, as in "@@myobject." This naming convention is reserved for global variables, which are system-defined variables that Adaptive Server updates on an ongoing basis.

The case sensitivity of Adaptive Server is set when the server is installed and can be changed only by a System Administrator. To see the setting for your server, execute:

sp_helpsort

On a server that is not case-sensitive, the identifiers MYOBJECT, myobject, and MyObject (and all combinations of case) are considered identical. You can create only one of these objects, but you can use any combination of case to refer to that object.

No embedded spaces are allowed in identifiers, and none of the SQL reserved keywords can be used. The reserved words are listed in the Adaptive Server Reference Manual.

You can use the function valid_name to determine if an identifier you have created is acceptable to Adaptive Server. For example:

select valid_name ("string")

string is the identifier you want to check. If string is not valid as an identifier, Adaptive Server returns a 0 (zero). If string is a valid identifier, Adaptive Server returns a number other than 0. Adaptive Server returns a 0 if illegal characters are used or if string is longer than 30 bytes.

In multibyte character sets, a wider range of characters is available for use in identifiers. For example, on a server with the Japanese language installed, the following types of characters can be used as the first character of an identifier: Zenkaku or Hankaku Katakana, Hiragana, Kanji, Romaji, Cyrillic, Greek, or ASCII.

Although Hankaku Katakana characters are legal in identifiers on Japanese systems, they are not recommended for use in heterogeneous systems. These characters cannot be converted between the EUC-JIS and Shift-JIS character sets.

The same is true for some 8-bit European characters. For example, the character "&OElig;," the OE ligature, is part of the Macintosh character set (code point 0xCE), but does not exist in the ISO 8859-1 (iso_1) character set. If "&OElig;" exists in data being converted from the Macintosh to the ISO 8859-1 character set, it causes a conversion error.

If an object identifier contains a character that cannot be converted, the client loses direct access to that object.

Delimited identifiers are object names enclosed in double quotes. Using delimited identifiers allows you to avoid certain restrictions on object names. You can use double quotes to delimit table, view, and column names; you cannot use them for other database objects.

Delimited identifiers can be reserved words, can begin with non-alphabetic characters, and can include characters that would not otherwise be allowed. They cannot exceed 28 bytes. A pound sign (#) is illegal as a first character of any quoted identifier. (This restriction applies to Adaptive Server 11.5 and all later versions.)

Before you create or reference a delimited identifier, you must execute:

set quoted_identifier on

This allows Adaptive Server to recognize delimited identifiers. Each time you use the quoted identifier in a statement, you must enclose it in double quotes. For example:

create table "1one"(col1 char(3))
select * from "1one"
create table "include spaces" (col1 int)

Delimited identifiers cannot be used with bcp, may not be supported by all front-end products, and can produce unexpected results when used with system procedures.

While the quoted_identifier option is turned on, do not use double quotes around character or date strings; use single quotes instead. Delimiting these strings with double quotes causes Adaptive Server to treat them as identifiers. For example, to insert a character string into col1 of 1table, use:

insert "1one"(col1) values ('abc')

not:

insert "1one"(col1) values ("abc")

To insert a single quote into a column, use two consecutive single quotation marks. For example, to insert the characters "a'b" into col1, use:

insert "1one"(col1) values('a''b')

Syntax that includes quotes

When the quoted_identifier option is set to on, you do not need to use double quotes around an identifier if the syntax of the statement requires that a quoted string contain an identifier. For example:

create table '1one' (c1 int)

The quotes are included in the name of table '1one':

select object_id('1one')

-----------------------
  896003192

You can include an embedded double quote in a quoted identifier by doubling the quote:

create table "embedded""quote" (c1 int)

However, there is no need to double the quote when the statement syntax requires the object name to be expressed as a string:

select object_id('embedded"quote')

The names of database objects do not have to be unique in a database. However, column names and index names must be unique within a table, and other object names must be unique for each owner within a database. Database names must be unique in Adaptive Server.

If you try to create a column using a name that is not unique in the table, or to create another database object, such as a table, a view, or a stored procedure, with a name that you have already used in the same database, Adaptive Server responds with an error message.

You can uniquely identify a table or column by adding other names that qualify it. The database name, the owner's name, and, for a column, the table name or view name may be used to create a unique ID. Each of these qualifiers is separated from the next by a period:

For example, if the user "sharon" owns the authors table in the pubs2 database, the unique identifier of the city column in that table is:

pubs2.sharon.authors.city

The same naming syntax applies to other database objects. You can refer to any object in a similar fashion:

pubs2.dbo.titleview

dbo.postalcoderule

If the quoted_identifier option of the set command is on, you can use double quotes around individual parts of a qualified object name. Use a separate pair of quotes for each qualifier that requires quotes. For example, use:

database.owner."table_name"."column_name"

rather than:

database.owner."table_name.column_name"

The full naming syntax is not always allowed in create statements because you cannot create a view, procedure, rule, default, or trigger in a database other than the one you are currently in. The naming conventions are indicated in the syntax as:

[[database.]owner.]object_name

or

[owner.]object_name

The default value for owner is the current user, and the default value for database is the current database. When you reference an object in any SQL statement, other than a create statement, without qualifying it with the database name and owner name, Adaptive Server first looks at all the objects you own, and then at the objects owned by the Database Owner. As long as Adaptive Server is given enough information to identify an object, you need not type every element of its name. You can omit intermediate elements and indicate their positions with periods:

database..table_name

In the example above, you must include the starting element if you are using this syntax to create tables. If you omit the starting element, you would create a table named ..mytable. The naming convention prevents you from performing certain actions on such a table, such as cursor updates.

When qualifying a column name and a table name in the same statement, use the same naming abbreviations for each; they are evaluated as strings and must match, or an error is returned. Here are two examples with different entries for the column name. The second example does not run because the syntax for the column name does not match the syntax for the table name.

select pubs2.dbo.publishers.city 
from pubs2.dbo.publishers

city
----------------------- 
Boston 
Washington 
Berkeley

select pubs2.sa.publishers.city 
from pubs2..publishers 

The column prefix "pubs2.sa.publishers" does not match a table name or alias name used in the query.

You can execute stored procedures on a remote Adaptive Server. The results from the stored procedure display on the terminal that calls the procedure. The syntax for identifying a remote server and the stored procedure is:

[execute] server.[database].[owner].procedure_name

You can omit the execute keyword when the remote procedure call (RPC) is the first statement in a batch. If other SQL statements precede the RPC, you must use execute or exec. You must give the server name and the stored procedure name. If you omit the database name, Adaptive Server looks for procedure_name in your default database. If you give the database name, you must also give the procedure owner's name, unless you own the procedure or the procedure is owned by the Database Owner.

The following statements execute the stored procedure byroyalty in the pubs2 database located on the GATEWAY server:

GATEWAY.pubs2.dbo.byroyalty
GATEWAY.pubs2..byroyalty

GATEWAY...byroyalty

declare @var int 
exec GATEWAY...byroyalty

See the System Administration Guide for information on setting up Adaptive Server for remote access. A remote server name (GATEWAY in the previous example) must match a server name in your local Adaptive Server's interfaces file. If the server name in interfaces is in uppercase letters, you must also use uppercase letters in the RPC.