Chapter 3: Basic Transact-SQL Programming Constructs


All modern relational database management systems are based on an implementation of SQL (Structured Query Language). Most vendors have extended SQL into a more sophisticated programming language. The ANSI committee has standardized the language several times, of which ANSI SQL-92 is the latest specification. Unfortunately (or fortunately—depending on your point of view), each vendor has created its own version of this specification to extend ANSI SQL.

The language in use in Microsoft SQL Server is called Transact-SQL (T-SQL). It complies with the ANSI SQL-92 standard, and you can use this ANSI SQL-compliant language to select, update, insert, and delete records from tables.

T-SQL Identifierss

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 (3 0). If Microsoft again changes the way identifiers are named, procedures using sysname will automatically be upgraded.

The following are valid identifiers:

  • Cost

  • Premium 36

  • prCalcCost

  • idx_User

  • @@Make

  • #Equipment

  • [First Name]

  • #Equipment ID

  • [User]

  • [User. Group]

Note 

Although delimiters can he 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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net