Objectives

Today's material supplements the previous presentations, as Transact-SQL is a supplement to the accepted SQL standard. Today's goals are to

• Identify one of the popular extensions to SQL

• Outline the major features of Transact-SQL

• Provide practical examples to give you an understanding of how Transact-SQL is used

An Overview of Transact-SQL

Day 13, "Advanced SQL Topics," briefly covered static SQL. The examples on Day 13 depicted the use of embedded SQL in third-generation programming languages such as C. With this method of programming, the embedded SQL code does not change and is, therefore, limited. On the other hand, you can write dynamic SQL to perform the same functions as a procedural programming language and allow conditions to be changed within the SQL code.

As we have mentioned during the discussion of virtually every topic in this book, almost every database vendor has added many extensions to the language. Transact-SQL is the Sybase and Microsoft SQL Server database product. Oracle's product is PL/SQL. Each of these languages contains the complete functionality of everything we have discussed so far. In addition, each product contains many extensions to the ANSI SQL standard.

Extensions to ANSI SQL

To illustrate the use of these SQL extensions to create actual programming logic, we are using Sybase and Microsoft SQL Server's Transact-SQL language. It contains most of the constructs found in third-generation languages, as well as some SQL Server-specific features that turn out to be very handy tools for the database programmer. (Other manufacturers' extensions contain many of these features and more.)

Who Uses Transact-SQL?

Everyone reading this book can use Transact-SQL--casual relational database programmers who occasionally write queries as well as developers who write applications and create objects such as triggers and stored procedures.

---

NOTE: Users of Sybase and Microsoft SQL Server who want to explore the true capabilities of relational database programming must use the Transact-SQL features.

---

The Basic Components of Transact-SQL

SQL extensions overcome SQL's limits as a procedural language. For example, Transact-SQL enables you to maintain tight control over your database transactions and to write procedural database programs that practically render the programmer exempt from exhausting programming tasks.

Day 19 covers the following key features of Transact-SQL:

• A wide range of data types to optimize data storage

• Program flow commands such as loops and IF-ELSE statements

• Use of variables in SQL statements

• Summarized reports using computations

• Diagnostic features to analyze SQL statements

• Many other options to enhance the standard language of SQL

Data Types

On Day 9, "Creating and Maintaining Tables," we discussed data types. When creating tables in SQL, you must specify a specific data type for each column.

---

NOTE: Data types vary between implementations of SQL because of the way each database server stores data. For instance, Oracle uses selected data types, whereas Sybase and Microsoft's SQL Server have their own data types.

---

Sybase and Microsoft's SQL Server support the following data types.

Character Strings

• char stores fixed-length character strings, such as STATE abbreviations, when you know that the column will always be two characters.

• varchar stores variable-length character strings, such as an individual's name, where the exact length of a name is not specified, for example, AL RAY to WILLIAM STEPHENSON.

• text stores strings with nearly unlimited size, such as a remarks column or description of a type of service.

Numeric Data Types

• int stores integers from -2,147,483,647 to +2,147,483,647.

• smallint stores integers from -32,768 to 32,767.

• tinyint stores integers from 0 to 255.

• float expresses numbers as real floating-point numbers with data precisions. Decimals are allowed with these data types. The values range from +2.23E-308 to +1.79E308.

• real expresses real numbers with data precisions from +1.18E-38 to +3.40E38.

DATE Data Types

• datetime values range from Jan 1, 1753 to Dec 31, 9999.

• smalldatetime values range from Jan 1, 1900 to Jun 6, 2079.

Money Data Types

• money stores values up to +922,337,203,685,477.5808.

• smallmoney stores values up to +214,748.3647.

Money values are inserted into a table using the dollar sign; for example:

insert payment_tbl (customer_id, paydate, pay_amt)
values (012845, "May 1, 1997", $2099.99)

Binary Strings

• binary stores fixed-length binary strings.

• varbinary stores variable-length binary strings.

• image stores very large binary strings, for example, photographs and other images.

bit: A Logical Data Type

The data type bit is often used to flag certain rows of data within a table. The value stored within a column whose data type is bit is either a 1 or 0. For example, the value 1 may signify the condition true, whereas 0 denotes a false condition. The following example uses the bit data type to create a table containing individual test scores:

create table test_flag
( ind_id int not null,
  test_results int not null,
  result_flag bit not null)

ANALYSIS:

The column result_flag is defined as a bit column, where the bit character represents either a pass or fail, where pass is true and fail is false.

Throughout the rest of the day, pay attention to the data types used when creating tables and writing Transact-SQL code.

---

NOTE: The code in today's examples uses both uppercase and lowercase. Although SQL keywords are not case sensitive in most implementations of SQL, always check your implementation.

---

Accessing the Database with Transact-SQL

All right, enough talk. To actually run the examples today, you will need to build the following database tables in a database named BASEBALL.

The BASEBALL Database

The BASEBALL database consists of three tables used to track typical baseball information: the BATTERS table, the PITCHERS table, and the TEAMS table. This database will be used in examples throughout the rest of today.

The BATTERS TABLE

NAME char(30)
TEAM int
AVERAGE float
HOMERUNS int
RBIS int

The table above can be created using the following Transact-SQL statement:

INPUT:

1> create database BASEBALL on default
2> go
1> use BASEBALL
2> go
1> create table BATTERS (
2> NAME char(30),
3> TEAM int,
4> AVERAGE float,
5> HOMERUNS int,
6> RBIS int)
7> go

ANALYSIS:

Line 1 creates the database. You specify the database BASEBALL and then create the table BATTERS underneath BASEBALL.

Enter the data in Table 19.1 into the BATTERS table.

---

NOTE: The command go that separates each Transact-SQL statement in the preceding