 |
|
T-SQL identifiers |
|
| 07 Feb 2007 | McGraw-Hill |
|
|
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 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.
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
