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 (
Requires Free Membership to View
- @), 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 identifiers.
Basic Transact-SQL Programming Constructs
Home: Introduction
Tip1: T-SQL Identifiers
Tip 2: Database object qualifiers
Tip 3: Character string data types
Tip 4: Date, time and number data types
Tip 5: Special data types-Part 1
Tip 6: Special data types-Part 2
Tip 7: Local variables
Tip 8: Global variables
Tip 9: Table variables
Tip 10: Flow control statements
Tip 11: Blocks, looping and conditional statements
Tip 12: Unconditional and scheduled execution
Tip 13: Transact SQL cursors
Tip 14: Cursor related statements
Tip 15: Problems and justified use of cursors
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.
This was first published in February 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation