| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Transact-SQL User's Guide |
![[-]](/dwicons/i_colpse.gif)
| Chapter 1 SQL Building Blocks |
|
| Transact-SQL extensions |
Transact-SQL enhances the power of SQL and minimizes the occasions on which users must resort to a programming language to accomplish a desired task. Transact-SQL's capabilities go beyond the ISO standards and the many commercial versions of SQL.
Most of the Transact-SQL enhancements (known as extensions) are summarized here. The Transact-SQL extensions for each command are in the Reference Manual.
The Transact-SQL compute clause extension is used with the row aggregate functions sum, max, min, avg, and count to calculate summary values. Queries that include a compute clause display results with both detail and summary rows. These reports resemble those produced by almost any Database Management System (DBMS) with a report generator. compute displays summary values as additional rows in the results, instead of as new columns. The compute clause is covered in Chapter 3, "Using Aggregates, Grouping, and Sorting."
Transact-SQL provides control-of-flow language that can be used as part of any SQL statement or batch. These constructs are available: begin...end, break, continue, declare, goto label, if...else, print, raiserror, return, waitfor, and while. Local variables can be defined with declare and assigned values. A number of predefined global variables are supplied by the system.
Transact-SQL also supports case expressions, which include the keywords case, when, then, coalesce, and nullif. case expressions replace the if statements of standard SQL. case expressions are allowed anywhere a value expression is used.
One of the most important Transact-SQL extensions is the ability to create stored procedures. A stored procedure is a collection of SQL statements and optional control-of-flow statements stored under a name. The creator of a stored procedure can also define parameters to be supplied when the stored procedure is executed.
The ability to write your own stored procedures greatly enhances the power, efficiency, and flexibility of the SQL database language. Since the execution plan is saved after stored procedures are run, stored procedures can subsequently run much faster than standalone statements.
Adaptive Server-supplied stored procedures, called system procedures, aid in Adaptive Server system administration. Chapter 14, "Using Stored Procedures," discusses system procedures and explains how to create stored procedures. System procedures are discussed in detail in the Reference Manual.
Users can execute stored procedures on remote servers. All Transact-SQL extensions support return values from stored procedures, user-defined return status from stored procedures, and the ability to pass parameters from a procedure to its caller.
An extended stored procedure (ESP) has the interface of a stored procedure, but instead of containing SQL statements and control-of-flow statements, it executes procedural language code that has been compiled into a dynamic link library (DLL).
The procedural language in which an ESP function is written can be any language capable of calling C language functions and manipulating C datatypes.
ESPs allow Adaptive Server to perform a task outside the RDBMS in response to an event occurring within the database. For example, you could use an ESP to send an e-mail notification or network-wide broadcast in response to an event occurring within the Relational Database Management System (RDBMS).
There are some Adaptive Server-supplied ESPs, called system extended stored procedures. One of these, xp_cmdshell, allows you to execute an operating system command from within Adaptive Server. Chapter 15, "Using Extended Stored Procedures," describes ESPs. The Reference Manual includes detailed information about system ESPs.
ESPs are implemented by an Open Server(TM) application called XP Server(TM), which runs on the same machine as Adaptive Server. Remote execution of a stored procedure is called a remote procedure call (RPC). Adaptive Server and XP Server communicate through RPCs. XP Server is automatically installed with Adaptive Server.
A trigger is a stored procedure that instructs the system to take one or more actions when a specific change is attempted. By preventing incorrect, unauthorized, or inconsistent changes to data, triggers help maintain the integrity of a database.
Triggers can also protect referential integrity--enforcing rules about the relationships among data in different tables. Triggers go into effect when a user attempts to modify data with an insert, delete, or update command.
Triggers can nest to a depth of 16 levels, and can call local or remote stored procedures or other triggers.
Transact-SQL provides keywords for maintaining entity integrity (ensuring that a value is supplied for every column requiring one) and domain integrity (ensuring that each value in a column belongs to the set of legal values for that column). Defaults and rules define integrity constraints that come into play during data entry and modification.
A default is a value linked to a particular column or datatype, and inserted by the system if no value is provided during data entry. Rules are user-defined integrity constraints linked to a particular column or datatype, and enforced at data entry time. Rules and defaults are discussed in Chapter 12, "Defining Defaults and Rules for Data."
A number of error handling techniques are available to the Transact-SQL programmer, including the ability to capture return status from stored procedures, define customized return values from stored procedures, pass parameters from a procedure to its caller, and get reports from global variables such as @@error. The raiserror and print statements, in combination with control-of-flow language, can direct error messages to the user of a Transact-SQL application. Developers can localize print and raiserror to use different languages.
set options customize the display of results, show processing statistics, and provide other diagnostic aids for debugging your Transact-SQL programs. All set options except showplan and char_convert take effect immediately.
The following paragraphs list the available set options. For more information, refer to the Reference Manual.
parseonly, noexec, prefetch, showplan, rowcount, nocount, and tablecount control the way a query is executed. The statistics options display performance statistics after each query. flushmessage determines when Adaptive Server returns messages to the user. See the Performance and Tuning Guide for more information.
arithabort determines whether Adaptive Server aborts queries with arithmetic overflow and numeric truncation errors. arithignore determines whether Adaptive Server prints a warning message if a query results in an arithmetic overflow. For more information, see "Arithmetic errors".
offsets and procid are used in DB-Library(TM) to interpret results from Adaptive Server.
datefirst, dateformat, and language affect date functions, date order, and message display.
char_convert controls character-set conversion between Adaptive Server and a client.
textsize controls the size of text or image data returned with a select statement. See "Text functions used for text and image data".
cursor rows and close on endtran affect the way Adaptive Server handles cursors. See "Fetching data rows using cursors".
identity_insert allows or prohibits inserts that affect a table's IDENTITY column. See "Gaps due to insertions, deletions, identity grab size, and rollbacks".
chained and transaction isolation level control how Adaptive Server handles transactions. See "Selecting the transaction mode and isolation level".
self_recursion allows Adaptive Server to handle triggers that cause themselves to fire. See "Trigger self-recursion".
ansinull, ansi_permissions, and fipsflagger control whether Adaptive Server flags the use of nonstandard SQL. string_rtruncation controls whether Adaptive Server raises an exception error when truncating a char or nchar string. See "Compliance to ANSI standards".
quoted_identifier controls whether Adaptive Server treats character strings enclosed in double quotes as identifiers. See "Delimited identifiers" for more information.
role controls the roles granted to you. For information about roles, see the System Administration Guide.
Other unique or unusual features of Transact-SQL include:
The following extensions to SQL search conditions: modulo operator (%), negative comparison operators (!>, !<, and !=), bitwise operators (-, ^, |, and &), join operators (*= and =*), wildcard characters ([ ] and -), and the not operator (^). See Chapter 2, "Queries: Selecting Data from a Table."
Fewer restrictions on the group by clause and the order by clause. See Chapter 3, "Using Aggregates, Grouping, and Sorting."
Subqueries, which can be used almost anywhere an expression is allowed. See Chapter 5, "Subqueries: Using Queries Within Other Queries."
Temporary tables and other temporary database objects, which exist only for the duration of the current work session, and disappear thereafter. See Chapter 7, "Creating Databases and Tables."
User-defined datatypes built on Adaptive Server-supplied datatypes. See Chapter 6, "Using and Creating Datatypes," and Chapter 12, "Defining Defaults and Rules for Data."
The ability to insert data from a table into that same table. See Chapter 8, "Adding, Changing, and Deleting Data."
The ability to extract data from one table and put it into another with the update command. See Chapter 8, "Adding, Changing, and Deleting Data."
The ability to remove data based on data in other tables using the join in a delete statement. See Chapter 8, "Adding, Changing, and Deleting Data."
A fast way to delete all rows in a specified table and reclaim the space they took up with the truncate table command. See Chapter 8, "Adding, Changing, and Deleting Data."
IDENTITY columns, which provide system-generated values that uniquely identify each row within a table. See Chapter 8, "Adding, Changing, and Deleting Data."
Updates and selections through views. Unlike most other versions of SQL, Transact-SQL places no restrictions on retrieving data through views, and few restrictions on updating data through views. See Chapter 9, "Views: Limiting Access to Data."
Dozens of built-in functions. See Chapter 10, "Using the Built-In Functions in Queries."
Options to the create index command for fine-tuning aspects of performance determined by indexes, and controlling the treatment of duplicate keys and rows. See Chapter 11, "Creating Indexes on Tables."
Control over what happens when a user attempts to enter duplicate keys in a unique index, or duplicate rows in a table. See Chapter 11, "Creating Indexes on Tables."
Bitwise operators for use with integer and bit type columns. See "Bitwise operators" and Chapter 6, "Using and Creating Datatypes."
Support for text and image datatypes. See Chapter 6, "Using and Creating Datatypes."
The ability to gain access to both Sybase and non-Sybase databases. With Component Integration Services, you can accomplish the following types of actions between tables in remote, heterogeneous servers: access remote tables as if they were local, perform joins, transfer data between tables, maintain referential integrity, provide applications such as PowerBuilderŪ with transparent access to heterogeneous data, and use native remote server capabilities. For more information, see the Component Integration Services User's Guide.
|
|