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) Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility SQL/Transact SQL (T-SQL) Research Microsoft SQL Server 2008 Q&A: SQL Server 2008 a better fit for consolidation End of life comes for SQL Server 2005 SP2, 2008 What's new for installation with SQL Server 2008? SQL Server Reporting Services 2008 offers faster speeds, new variations Microsoft SQL Server 2008 Learning Guide Understanding transparent data encryption in SQL Server 2008 Working with sparse columns in SQL Server 2008 Implementing SQL Server 2008 FILESTREAM functionality Microsoft renames SQL Server release, adds data services New GROUP BY option provides better data control in SQL Server 2008 Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) Collaboration Data Objects  (SearchSQLServer.com) commit  (SearchSQLServer.com) container  (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