Chapter 1: SQL Building Blocks

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-2 specifies the byte limit for the different identifier types.

**Table 1-2: 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
LWP or dynamic statement name	cache name
function name	logical device name
time range name	segment name
funcation 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.

View this book as PDF