All rights reserved.
Teach Yourself SQL in 21 Days, Second
Edition
- Day 19 -
Transact-SQL: An Introduction
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.