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 $.
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.
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:
- [First Name]
- "Equipment ID"
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
- 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.