Basic objects of T-SQL in SQL Server 2008

If you have any comments or questions about the information presented in this book chapter, please send an email to editor@searchsqlserver.com.

Learn the elementary objects and basic operators supported by the Transact-SQL language in SQL Server 2008 in this excerpt from "Microsoft SQL Server 2008: A Beginner's Guide." You'll find out about the basic objects and operators of T-SQL, including constants, identifiers and delimiters, and you'll also discover the corresponding data types that relate to each object and operator. Author Dusan Petkovic also describes how NULL values, scalar objects and global variables operate in SQL Server 2008.

The language of Database Engine, Transact-SQL, has the same basic features as other common programming languages:

• Literal values (also called constants)
• Delimiters
• Comments
• Identifiers
• Reserved keywords

The following sections describe these features.

A literal value is an alphanumerical, hexadecimal, or numeric constant. A string constant contains one or more characters of the character set enclosed in two single straight quotation marks (' ') or double straight quotation marks (" ") (single quotation marks are preferred due to the multiple uses of double quotation marks, as discussed in a moment). If you want to include a single quotation mark within a string delimited by single quotation marks, use two consecutive single quotation marks within the string. Hexadecimal constants are used to represent nonprintable characters and other binary data. Each hexadecimal constant begins with the characters '0x' followed by an even number of characters or numbers. Examples 4.1 and 4.2 illustrate some valid and invalid string constants and hexadecimal constants.

Example 4.1
Some valid string constants and hexadecimal constants follow:

'Philadelphia'
"Berkeley, CA 94710"
'9876'
'Apostrophe is displayed like this: can''t' (note the two consecutive single quotation marks)
0x53514C0D

Example 4.2
The following are not string constants:

'AB'C' (odd number of single quotation marks)
'New York" (same type of quotation mark—single or double—must be used at each end of the string)

The numeric constants include all integer, fixed-point, and floating-point values with and without signs (see Example 4.3).

Example 4.3
The following are numeric constants:

A constant always has a data type and a length, and both depend on the format of the constant. Additionally, every numeric constant has a precision and a scale factor. (The data types of the different kinds of literal values are explained later in this chapter.)

In Transact-SQL, double quotation marks have two meanings. In addition to enclosing strings, double quotation marks can also be used as delimiters for so-called delimited identifiers. Delimited identifiers are a special kind of identifier usually used to allow the use of reserved keywords as identifiers and also to allow spaces in the names of database objects.

Note: Differentiation between single and double quotation marks was first introduced in the SQL92 standard. In the case of identifiers, this standard differentiates between regular and delimited identifiers. Two key differences are that delimited identifiers are enclosed in double quotation marks and are case sensitive. (Transact-SQL also supports the use of square brackets instead of double quotation marks.) Double quotation marks are only used for delimiting strings. Generally, delimited identifiers were introduced to allow the specification of identifiers, which are otherwise identical to reserved keywords. Specifically, delimited identifiers protect you from using names (identifiers, variable names) that could be introduced as reserved keywords in one of the future SQL standards. Also, delimited identifiers may contain characters that are normally illegal within identifier names, such as blanks.

In Transact-SQL, the use of double quotation marks is defined using the QUOTED_IDENTIFIER option of the SET statement. If this option is set to ON, which is the default value, an identifier in double quotation marks will be defined as a delimited identifier. In this case, double quotation marks cannot be used for delimiting strings.

There are two different ways to specify a comment in a Transact-SQL statement. Using the pair of characters /* and */ marks the enclosed text as a comment. In this case, the comment may extend over several lines. Furthermore, the characters -- (two hyphens) indicate that the remainder of the current line is a comment. (The two hyphens -- comply with the ANSI SQL standard, while /* and */ are the extensions of Transact-SQL.)

In Transact-SQL, identifiers are used to identify database objects such as databases, tables, and indices. They are represented by character strings that may include up to 128 characters and can contain letters, numerals, or the following characters: _, @, #, and $. Each name must begin with a letter or one of the following characters: _, @, or #. The character # at the beginning of a table or stored procedure name denotes a temporary object, while @ at the beginning of a name denotes a variable. As indicated earlier, these rules don't apply to delimited identifiers (also known as quoted identifiers), which can contain, or begin with, any character (other than the delimiters themselves).

Each programming language has a set of names with reserved meanings, which must be written and used in the defined format. Names of this kind are called reserved keywords. Transact-SQL uses a variety of such names, which, as in many other programming languages, cannot be used as object names, unless the objects are specified as delimited or quoted identifiers.

Note: In Transact-SQL, the names of all data types and system functions, such as CHARACTER and INTEGER, are not reserved keywords. They can therefore be used for denoting objects. (Do not use data types and system functions as object names! Such a use makes Transact-SQL statements difficult to read and understand.)

TABLE OF CONTENTS    Part 1: The basic objects of T-SQL in SQL Server 2008    Part 2: Using T-SQL data types in SQL Server 2008    Part 3: SQL Server 2008 function types in T-SQL    Part 4: Additional T-SQL operations in SQL Server 2008

This chapter excerpt from Microsoft SQL Server 2008 : A Beginner's Guide by Dusan Petkovic, is printed with permission from McGraw-Hill Osborne Media, Copyright 2008. Click here for the chapter download or purchase the book here.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL/Transact SQL (T-SQL) The sqlcmd utility in SQL Server How to create a SQL inner join and outer join: Basics to get started Avoid cursors in SQL Server with these methods to loop over records Implementing security audit in SQL Server 2008 New datetime data types in SQL Server 2008 offer flexibility Using T-SQL data types in SQL Server 2008 SQL Server 2008 function types in T-SQL Additional T-SQL operations in SQL Server 2008 Using DATEADD and DATEDIFF to calculate SQL Server datetime values Manipulate column names in a SQL Server table SQL/Transact SQL (T-SQL) Research SQL Server 2008 (Katmai) SQL Server Management Studio 2008: New features, part 1 Implementing security audit in SQL Server 2008 What's new in SQL Server 2008 Reporting Services? New security features in SQL Server 2008 leave some work for you New datetime data types in SQL Server 2008 offer flexibility Using T-SQL data types in SQL Server 2008 SQL Server 2008 function types in T-SQL Additional T-SQL operations in SQL Server 2008 SQL Server 2008 Integration Services delivers new features Change data capture in SQL Server 2008 improves BI reporting accuracy SQL Server performance and tuning Top 10 SQL Server Tips of 2008 Tutorial: Performance tuning SQL Server via queries, indexes and more SQL Server Management Studio 2008: New features, part 1 Tutorial: SQL Server indexing tips to improve performance SQL Server virtualization pros and cons: Weigh the performance impact How to configure storage in SQL Server DB with more writes than reads Avoid cursors in SQL Server with these methods to loop over records Solve SQL Server errors and more from the DBA trenches -- part 2 Tutorial: Learn SQL Server basics from A-Z FAQ: Troubleshooting SQL Server Reporting Services

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) commit  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary