Transact-SQL and Utilities

Introduction

This chapter describes the rich toolset that Transact-SQL provides, including:

• ``The compute Clause''

• ``Control-of-Flow Language''

• ``Generated Primary Keys''

• ``Stored Procedures''

• ``Constraints''

• ``Rules''

• ``Defaults''

• ``Triggers''

• ``Cursors''

• ``Views''

• ``Transact-SQL System Administration Capabilities''

• ``Error Handling''

• ``text and image Datatypes''

• ``Transact-SQL's Debugger''

• ``Built-in Functions''

• ``Other Transact-SQL Extensions''

select title_id, type, price 

from titles 

where type like "%cook" 

order by type, price 

compute avg(price) by type 

 title_id type         price                     

 -------- ------------ ------------------------  

 MC3021   mod_cook                         2.99  

 MC2222   mod_cook                        19.99  

                       avg                       

                       ------------------------  

                                          11.49  

 

 title_id type         price                     

 -------- ------------ ------------------------  

 TC4203   trad_cook                       11.95  

 TC7777   trad_cook                       14.99  

 TC3218   trad_cook                       20.95  

                       avg                       

                       ------------------------  

                                          15.96  

You can define local variables with declare and assign them values. Several predefined global variables are supplied by the system.

Generated Primary Keys

A database owner or system administrator can use the set option identity_insert to explicitly insert values into identity columns. You can check the @@identity global variable to determine the last value inserted into an IDENTITY column.

Stored Procedures

Figure 5-1: A stored procedure

You can define business rules, transaction logic, and referential integrity as stored procedures. They are batches of Transact-SQL statements that can include commands to begin, commit, and rollback transactions, control the flow of program logic, declare variables, send messages, and initiate an action after a defined time delay.

Stored procedures are both reusable and sharable. When you define a stored procedure, SQL Server compiles it and stores it in shared procedure cache so that multiple users can make use of the same precompiled code. At run time, the application issues a procedure call and passes parameters, if there are any. SQL Server then executes the stored procedure without recompiling.

Stored procedures offer the following advantages over batches:

• They improve performance when you use them to implement frequently run queries because they are precompiled. Transact-SQL commands and batches must be parsed, compiled, and optimized each time they are run.

• They reduce network traffic because the client can issue a single statement (exec procedure_name) that sends the batch as a single I/O rather than sending each statement in the batch as a separate I/O.

• They aid in consistent, modular application development and eliminate redundancy because they can be reused.

• They provide security because users don't access the table directly¯they get a processed result. A user can execute a stored procedure even if he or she does not have permissions to modify tables that the procedure affects.

• They can be executed remotely if the local server and the remote server are set up for remote operations.

Rules

Defaults

Triggers

Figure 5-1 shows a delete trigger called del_title created on the titles table. The trigger fires whenever there is an attempt to delete a row in the titles table.

Figure 5-2: A trigger driven by a delete statement

Triggers can cause the statement that issued them to be rolled back if needed with an appropriate message to the user. They can also perform such actions as cascading deletes, archiving changed or deleted data, or firing other stored procedures.

Triggers provide more complex integrity rules than constraints or rules. The "trigger" aspect together with the "batch" aspect of a trigger make it a very useful feature. Here are the most common way triggers are used:

• To enforce referential integrity

• To enforce complex restrictions

• For notification

• To automate a process based on an insert, update, or delete operation

Views

Unlike many other versions of SQL, Transact-SQL places no restrictions on retrieving data through views, and relatively few restrictions on updating data through views.

Transact-SQL is also different from many other versions of SQL in that it allows you to redefine or change a view without forcing you to redefine other views that depend on it (unless the redefinition makes it impossible for SQL Server to translate the dependent view).

Cursors

A select operation with cursors offers the following advantages over a select operation without cursors:

• A program can take action on a row by row basis rather than on the entire set of selected rows

• Cursors bridge the gap between the set-oriented approach to RDBMS and row-oriented programming

• Cursors can be used in stored procedures and can operate on stored procedures

• They are built into SQL Server rather than client applications. They can be used in stored procedures and triggers, and can also be used during interactive SQL sessions for row-by-row processing

• They can remain open across transactions

• They can be used to update joined tables

• The locking strategy decouples transaction logging and lock granularity from the resource allocation of cursors

• They can be updated from Client-Library or Embedded SQL if they use the order by clause

• They allow Open Client and Embedded SQL applications to optionally fetch multiple rows at a time, providing better network performance

Error Handling

• Create user-defined error messages and call them from triggers or stored procedures

• Return error messages in any language available on the server, in the language specified by the user

• Capture return status from stored procedures

• Define customized return values from stored procedures

• Pass parameters from a procedure to its caller

• Get reports from global variables such as @@error

• Track transaction status with the global variable @@transtate

• Bind user-defined error messages to certain types of declared integrity constraints

• Return column names, object names, or other information to Open Client applications with user-defined error messages

Transact-SQL System Administration Capabilities

• Extensions to create table and create index permit tables and indexes to reside on separate database devices.

• An extension to create database that moves the database's transaction log to a separate database device. Additional commands for mirroring database devices helps ensure up-to-the-minute recovery from media failures.

• The shutdown command permits SQL Server and the Backup Server to be shut down from inside a Transact-SQL application, first doing a checkpoint in all databases. By default, the command allows current tasks to complete, or it can shut down the servers immediately.

• System functions, most of which return information from the system tables

• String functions for manipulating character and binary values

• Text functions for manipulating text and image values

• Mathematical functions for trigonometry, geometry, and other number handling

• Date functions for manipulating datetime values

• Type conversion functions for converting expressions from one datatype to another, and for formatting dates in a wide variety of styles

Transact-SQL's Debugger

Other Transact-SQL Extensions

• Fewer restrictions on the group by clause and the order by clause

• User-defined datatypes built on SQL Server-supplied datatypes

• Subqueries that can be used almost anywhere an expression is allowed

• Temporary tables and other temporary database objects that exist only for the duration of the current work session and disappear at the end of the session or procedure

• Options to the create index command that fine-tune aspects of performance determined by indexes and control the treatment of duplicate keys and rows

• User control over attempts to enter duplicate keys in a unique index or duplicate rows in a table

• The ability to insert data from a table into the same table

• The ability to extract data from one table and put it into another table with the update command

• The ability to use the delete command to remove data based on data in other tables that are used in a join

• A fast way to delete all rows in a specified table and reclaim the space they occupied with the truncate table command

• Bitwise operators for use with integer and bit type columns

• Extensions of cursor capabilities