Identifiers

Conventions for naming database objects apply throughout Adaptive Server software and documentation. Most user-defined identifiers can be up to 255 bytes in length; other identifiers can be only up to 30 bytes. In either case, the byte limit is independent of whether or not multibyte characters are used. Table 1-4 specifies the byte limit for the different identifier types.

Table 1-4: Byte limits for identifiers

255-byte-limit identifiers

30-byte-limit identifiers

table name

cursor name

column name

server name

index name

host name

view name

login name

user-defined datatype

password

trigger name

host process identification

default name

application name

rule name

initial language name

constraint name

character set name

stored procedure name

user name

variable name

group name

JAR name

database name

Lightweight processes (LWPs) or dynamic statement name

cache name

function name

logical device name

time range name

segment name

function name

session name

application context name

execution class name

engine name

quiesce tag name

You must declare the first character of an identifier as an alphabetic character in the character set definition in use on Adaptive Server. You can also use the @ sign or _ (underscore character). 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.” If you create a temporary table with a name requiring fewer than 238 bytes, Adaptive Server adds a 17-byte suffix to ensure that the table name is unique. If you create a temporary table with a name of more than 238 bytes, Adaptive Server uses only the first 238 bytes, and then adds the 17-byte 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 automatically updates.

The case sensitivity of Adaptive Server is set during server installation 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.

You cannot use embedded spaces, or SQL reserved words in identifiers. Use valid_name to determine if an identifier you have created is acceptable to Adaptive Server:

select valid_name ("@name", 255)

See Chapter 5 “Reserved Words” and Chapter 2, “Transact-SQL Functions,” in Reference Manual: Building Blocks