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-2 specifies the byte limit for the different identifier types.
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 |
LWP 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 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. The syntax of this function is:
(character_expression [, maximum_length ]
If you do not use the optional second parameter maximum_length, valid_name returns 0 for identifiers more than 30 bytes long. If you do use maximum_length, and the identifier length is larger than the maximum_length argument, valid_name returns 0, and the indentifier is invalid. However, you can use any number up to 255 in maximum_length; if you use 255, valid_length returns 0 for identifiers more than 255 bytes long.
For example:
select valid_name ("string", 255)
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 255 bytes.
