Get started Bring yourself up to speed with our introductory content.

T-SQL identifiers

T-SQL Identifiers are used in all databases, servers and database objects in SQL Server. Identifiers are assigned to identify and object when it is created, but can be changed after. Rules and tips for creating identifiers are outlined in the following excerpt.

All databases, servers, and database objects in SQL Server (such as tables, constraints, stored procedures, views, columns, and data types) must have unique names, or identifiers. They are assigned when an object is created, and used thereafter to identify the object. The identifier for the object may, if needed, be changed.
The following are the rules for creating identifiers:

  • Identifiers may have between 1 and 128 characters. There are exceptions to this rule: certain objects are limited (for instance, temporary tables can have identifiers up to only 116 characters long). Before Microsoft SQL Server 7.0, identifiers were limited to 30 characters.
  • The first character of the identifier must be a letter, underscore ( _ ), at sign (@), or number sign (#). The first letter must be defined in the Unicode 2.0 standard. Among other letters, Latin letters a–z and A–Z can be used as a first character. Some characters (@ and #) have special meanings in T-SQL. They act as signals to SQL Server to treat their carriers differently.
  • Subsequent characters must be letters from the Unicode 2.0 standard, or decimal digits, or one of the special characters @, #, _, or $.
  • SQL Server reserved words should not be used as object identifiers.
  • Identifiers cannot contain spaces or other special characters except for @, #, _, or $.

TIP
You can check which identifiers are valid by using the system stored procedure sp_validname.

If the identifier does not comply with one of the previous rules, it is referred to as a delimited identifier, and it must be delimited by double quotes (" ") or square brackets ( [ ] ) when referenced in T-SQL statements. You can change the default behavior if you use the Set Quoted_Identifier Off statement. The role of single and double quotes will be reversed. Single quotes will delimit identifiers, and double quotes will delimit strings.

As an interim migration aid, you can specify the compatibility mode in which SQL Server will run using the system stored procedure sp_dbcmptlevel. Changing the compatibility mode will affect the way in which SQL Server interprets identifiers. You should check Books OnLine for more information if you are running in any compatibility mode other than 80.

NOTE
The designers of Microsoft SQL Server have created a special system data type called sysname to control the length of identifiers. You should use it—instead of nvarchar(128)—for variables that will store database object identifiers. Before SQL Server 7, this type was a synonym for varchar(30). If Microsoft again changes the way identifiers are named, procedures using sysname will automatically be upgraded.

The following are valid identifiers:
 

  • Cost
  • Premium36
  • prCalcCost
  • idx_User
  • @@Make
  • #Equipment
  • [First Name]
  • "Equipment ID"
  • [User]
  • [User.Group]

    NOTE 
    Although delimiters can be used to assign identifiers that are also keywords (such as User) to objects, this practice is not recommended. You will save a substantial amount of time if you use regular identifier.

TABLE OF CONTENTS


The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.

 


 

 

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close