About this book

This manual, the Transact-SQL User’s Guide, documents Transact-SQL®, an enhanced version of the SQL relational database language. The Transact-SQL User’s Guide is intended for both beginners and those who have experience with other implementations of SQL.

Audience

Users of the Sybase® Adaptive Server™ Enterprise database management systems who are unfamiliar with SQL can consider this guide as a textbook and start at the beginning. Novice SQL users should concentrate on the first part of this book. The second part describes advanced topics.

This manual is useful both as a review, for those acquainted with other versions of SQL, and as a guide to Transact-SQL enhancements. SQL experts should study the capabilities and features that Transact-SQL has added to standard SQL, especially the material on stored procedures.

How to use this book

This book is a complete guide to Transact-SQL. It contains an introductory chapter, which gives an overview of SQL. The remaining chapters are divided into two main parts: Basic Concepts and Advanced Topics.

Chapter 1, “SQL Building Blocks,” describes the naming conventions used by SQL and the enhancements (also known as extensions) added by Transact-SQL. It also includes a description of how to get started with Transact-SQL using the isql utility. Sybase recommends that all users read this chapter.

“Part 1: Basic Concepts” includes Chapters 2–9. These chapters introduce you to the basic functionality of SQL. Users new to SQL should become familiar with the concepts described in these chapters before moving on to Part 2. Experienced users of SQL may want to skim through these chapters to learn about the Transact-SQL extensions introduced there and to review the material.

“Part 2: Advanced Topics” includes Chapters 10–18. These chapters describe Transact-SQL in more detail. Most of the Transact-SQL extensions are described here. Users familiar with SQL, but not Transact-SQL, should concentrate on these chapters.

The examples in this guide are based on the pubs2 and, where noted, pubs3 sample databases. For best use of the Transact-SQL User’s Guide, new users should work through the examples. Ask your System Administrator how to get a clean copy of pubs2 and pubs3. For a complete description of these databases, see Appendix A, “The pubs2 Database,” and Appendix B, “The pubs3 Database.”

You can use Transact-SQL with the Adaptive Server standalone program isql. The isql program is a utility program called directly from the operating system.

Other sources of information

Use the Sybase Technical Library CD and the Technical Library Product Manuals Web site to learn more about your product:

Technical Library CD contains product manuals and is included with your software. The DynaText browser (downloadable from Product Manuals) allows you to access technical information about your product in an easy-to-use format.

Refer to the Technical Library Installation Guide in your documentation package for instructions on installing and starting the Technical Library.
Technical Library Product Manuals Web site is an HTML version of the Technical Library CD that you can access using a standard Web browser. In addition to product manuals, you will find links to the Technical Documents Web site (formerly known as Tech Info Library), the Solved Cases page, and Sybase/Powersoft newsgroups.

To access the Technical Library Product Manuals Web site, go to Product Manuals.

Sybase certifications on the Web

Technical documentation at the Sybase Web site is updated frequently.

For the latest information on product certifications

Point your Web browser to Technical Documents.
Select Products from the navigation bar on the left.
Select a product name from the product list.
Select the Certification Report filter, specify a time frame, and click Go.
Click a Certification Report title to display the report.

For the latest information on EBFs and Updates

Point your Web browser to Technical Documents.
Select EBFs/Updates. Enter user name and password information, if prompted (for existing Web accounts) or create a new account (a free service).
Specify a time frame and click Go.
Select a product.
Click an EBF/Update title to display the report.

To create a personalized view of the Sybase Web site (including support pages)

Set up a MySybase profile. MySybase is a free service that allows you to create a personalized view of Sybase Web pages.

Point your Web browser to Technical Documents
Click MySybase and create a MySybase profile.

Conventions

The following sections describe conventions used in this manual.

Formatting SQL statements

SQL is a free-form language. There are no rules about the number of words you can put on a line or where you must break a line. However, for readability, all examples and syntax statements in this manual are formatted so that each clause of a statement begins on a new line. Clauses that have more than one part extend to additional lines, which are indented.

Font and syntax conventions

Table 1 shows the conventions for syntax statements that appear in this manual:

**Table 1: Font and syntax conventions for this manual**
Element	Example
Command names, command option names, utility names, utility flags, and other keywords use this font.	select sp_configure
Database names, datatypes, file names and path names use this font.	master database
Variables, or words that stand for values that you fill in, are in italics.	select column_name from table_name where search_conditions
Parentheses are to be typed as part of the command.	compute row_aggregate (column_name)
Curly braces indicate that you must choose at least one of the enclosed options. Do not type the braces.	{cash, check, credit}
Brackets mean choosing one or more of the enclosed options is optional. Do not type the brackets.	[anchovies]
The vertical bar means you may select only one of the options shown.	{die_on_your_feet \| live_on_your_knees \| live_on_your_feet}
The comma means you may choose as many of the options shown as you like, separating your choices with commas to be typed as part of the command.	[extra_cheese, avocados, sour_cream]
An ellipsis (...) means that you can repeat the last unit as many times as you like.	buy thing = price [cash \| check \| credit] [, thing = price [cash \| check \| credit] ]... You must buy at least one thing and give its price. You may choose a method of payment: one of the items enclosed in square brackets. You may also choose to buy additional things: as many of them as you like. For each thing you buy, give its name, its price, and (optionally) a method of payment.

Syntax statements (displaying the syntax and all options for a command) appear as follows:
```
sp_dropdevice [device_name]
```
or, for a command with more options:
```
select column_name 
          from table_name 
           where search_conditions
```
In syntax statements, keywords (commands) are in normal font and identifiers are in lowercase. Italic font shows user-supplied words.
Examples showing the use of Transact-SQL commands are printed like this:
```
select * from publishers
```

Examples of output from the computer appear as follows:

0736    New Age Books            Boston       MA 
0877    Binnet & Hardley         Washington   DC 
1389    Algodata Infosystems    Berkeley      CA

Case

In this manual, most of the examples are in lowercase. However, you can disregard case when typing Transact-SQL keywords. For example, SELECT, Select, and select are the same.

Adaptive Server’s sensitivity to the case of database objects, such as table names, depends on the sort order installed on Adaptive Server. You can change case sensitivity for single-byte character sets by reconfigured the Adaptive Server sort order. See the System Administration Guide for more information.

Expressions

Adaptive Server syntax statements use the following types of expressions.

**Table 2: Types of expressions used in syntax statements**
Usage	Definition
expression	Can include constants, literals, functions, column identifiers, variables, or parameters
logical expression	An expression that returns TRUE, FALSE, or UNKNOWN
constant expression	An expression that always returns the same value, such as “5+3” or “ABCDE”
float_expr	Any floating-point expression or expression that implicitly converts to a floating value
integer_expr	Any integer expression, or an expression that implicitly converts to an integer value
numeric_expr	Any numeric expression that returns a single value
char_expr	Any expression that returns a single character-type value
binary_expression	An expression that returns a single binary or varbinary value

If you need help

Each Sybase installation that has purchased a support contract has one or more designated people who are authorized to contact Sybase Technical Support. If you cannot resolve a problem using the manuals or online help, please have the designated person contact Sybase Technical Support or the Sybase subsidiary in your area.