Identifiers

Identifiers define the naming convention used by SQL Server for server names; database names; and database objects such as tables, views, columns, indexes, triggers, procedures, defaults, rules, and so on.

Rules for Identifiers

Identifiers can contain from 1 to 30 characters, including letters, symbols, and numbers. Identifiers have the following requirements:

• The first character of the identifier must be a letter or the symbols _ ,@ , #. The symbols @ and # have special meaning:
  • An identifier beginning with @ signifies a local variable.
  • An identifier beginning with # signifies a temporary object name. For a table or stored procedure, a single pound sign (#) signifies a local temporary object; a double pound sign (##) signifies a global temporary object. Names for temporary objects should not exceed 13 characters, including the # or ##, because SQL Server gives them an internal numeric suffix. For more information on creating these temporary objects, see the CREATE PROCEDURE and CREATE TABLE statements.
• Characters following the first character can include letters, digits, or the symbols #, $, or _.
• By default, no embedded spaces are allowed in identifiers; however, by using quoted identifiers, spaces and other special identifiers can be defined. For the rules and restrictions on quoted identifiers, see "Quoted Identifiers," later in this topic.

Identifiers as Object Names

Object names need not be unique in a database. However, column names and index names must be unique within a table or a view, and other object names must be unique for each owner within a database. Database names must be unique on SQL Server. You can uniquely identify a table or column by fully qualifying it¾the database name, the owner's name, and (for a column) the table or view name. Each of these qualifiers is separated from the next by a period:

[[database.]owner.]table_name
[[database.]owner.]view_name

The database and owner names are optional. The default value for owner is the current user; the default value for database is the current database.

Intermediate elements in a name can be omitted and their positions indicated by periods, as long as the system is given enough information to identify the object:

database..table_name
database..view_name

In the case of remote stored procedures, a procedure is qualified as follows:

server.database.owner.procedure

For example:

remotesvr.pubs.dbo.sp_who

Object Visibility (and Qualification) Rules

If you reference an object without qualifying it with the database name and owner name, SQL Server tries to find the object in the current database among the objects you own. If you don't own an object by that name, SQL Server looks for objects of that name owned by the database owner (qualified by database..obj_name). You need not use the database name to reference your objects in the current database. You must qualify objects owned by the database owner only if you own an object by the same name but want to use the object owned by the database owner. You must qualify objects owned by other users with the user's name.

The visibility for a stored procedure that begins with sp_ differ from regular procedures. For information, see the CREATE PROCEDURE statement.