Programming Constructs

The languages that interface to database management systems are sometimes divided into three categories:

• DML, Data Manipulation Language?This includes the ability to read and manipulate the data. Examples are SELECT, INSERT, DELETE, and UPDATE.

• DDL, Data Definition Language?Creating and altering the storage structures; an example is CREATE TABLE.

• DCL, Data Control Language?Defining permissions for data access; examples are GRANT, REVOKE, and DENY.

T-SQL includes other statements that can be useful, for instance, in tying together the DML statements in a stored procedure, such as IF, ELSE, and WHILE.

The IF Statement

The IF statement takes one argument: boolean_expression, which is an expression that can evaluate to TRUE or FALSE. The code to be conditionally executed is a statement block:

IF boolean_expression 
    statement_block
ELSE
    statement_block

You define a statement block with the statements BEGIN and END. If no statement block is defined, only the first statement following the IF statement is executed when the boolean_expression is true.

In Listing 26.11, a script checks for the existence of a table, prints a message if the table exists, and, if it does, drops the table.

Listing 26.11 Using the IF Statement to Perform Conditional Processing

IF OBJECTPROPERTY(OBJECT_ID('orders'), 'istable') = 1
BEGIN
    PRINT "Dropping orders Table"
    DROP TABLE orders
END
ELSE
    PRINT "Table orders does not exist"

WHILE, BREAK, and CONTINUE

The WHILE statement allows you to loop while an expression evaluates to true. The syntax for WHILE is as follows:

WHILE boolean_expression 
   statement_block

The statement block contains the BREAK or the CONTINUE statements. BREAK exits the WHILE loop, and CONTINUE skips any remaining statements in the statement block and evaluates the boolean_expression again. Listing 26.12 demonstrates a simple example of using a WHILE loop to repeatedly update the titles table until the average price of all books exceeds $25.

Listing 26.12 Using the WHILE Loop for Iterative Processing

/* loop until average price equals or exceeds $25*/
while (select avg (price) from titles) < $25
begin
    update titles set price = price * 1.05
    /* if more than 10 books are less than
    ** $15, continue processing */
    if (select count(*) from titles
           where price < $15) > 1
        continue
    /* If maximum price of any book exceeds
    ** $50, exit loop */
    if (select max(price) from titles) > $50
        break
end

CASE Expression

The CASE expression is an ANSI SQL-92 construct that allows for expressions that can evaluate a number of conditions and return a single result. CASE expressions are allowed wherever constant expressions are allowed, such as the SELECT list, the WHERE clause, GROUP BY, and ORDER BY. The CASE expression has two forms of syntax. The simple CASE expression compares an expression to a set of values to determine the result:

case expression 
     when value1 then result1
     [when value2 then result2]
     [...]
     [else resultN]
     END

The searched CASE expression evaluates a set of Boolean conditions to determine the result:

case 
    when Boolean_expression1 then expression1
    [[when Boolean_expression2 then expression2] [...]]
    [else expressionN]
    END

CASE expressions are an excellent tool to replace data values in columns (for example, code columns) with more meaningful values within your resultset, as shown in Listing 26.13.

Listing 26.13 Using the CASE Expression to Substitute for Column Values

select substring (title, 1, 20) as Title,
       case type
          when 'popular_comp' then ' Computer book'
          when 'mod_cook'  then ' Cookbook'
          when 'trad_cook'  then ' Cookbook'
          else 'Other book'
       end as Type,
       'Sales are ' +
       case
          when ytd_sales < 5000 then 'Poor'
          when ytd_sales between 5001 and 10000 then 'Good'
          when ytd_sales > 10000 then 'Awesome'
          else 'Unknown'
       end as Sales
   from titles
go

Title                 Type             Sales
--------------------  ---------------- --------------------
The Busy Executive's  Other book       Sales are Poor
Cooking with Compute  Other book       Sales are Poor
You Can Combat Compu  Other book       Sales are Awesome
Straight Talk About   Other book       Sales are Poor
Silicon Valley Gastr  Cookbook         Sales are Poor
The Gourmet Microwav  Cookbook         Sales are Awesome
The Psychology of     Other book       Sales are unknown
But Is It User Frien  Computer book    Sales are Good
Secrets of Silicon    Computer book    Sales are Poor
Net Etiquette         Computer book    Sales are unknown
Computer Phobic AND   Other book       Sales are Poor
Is Anger the Enemy?   Other book       Sales are Poor
Life Without Fear     Other book       Sales are Poor
Prolonged Data Depri  Other book       Sales are Poor
Emotional Security:   Other book       Sales are Poor
Onions, Leeks, and    Cookbook         Sales are Poor
Fifty Years in Bucki  Cookbook         Sales are Awesome
Sushi, Anyone?        Cookbook         Sales are Poor

The CASE expression can even be used in the SET clause of an UPDATE statement to conditionally set a value based on column values within the row, avoiding the need to use a cursor:

update titles 
   set price = case when ytd_sales < 500 then price *.75
                    when price > $15 then price * .90
                    else price * 1.15
               end

RETURN

RETURN is used to stop execution of a batch, stored procedure, or trigger. When used in a stored procedure, RETURN can take an integer as an argument. The value 0 indicates successful execution. The values -1 to -99 are reserved by Microsoft (currently, -1 to -14 are in use), so you should use values outside that range.

GOTO

GOTO (yes, there is a GOTO statement in T-SQL) branches to a defined label. GOTO can be useful for error handling in stored procedures, for example. The following is a code fragment from a stored procedure that checks for errors after each statement and exits the procedure with a return code if an error occurs:

[View full width]
BEGIN TRAN 
INSERT orders(customer_number) VALUES(1)
 IF @@ERROR <> 0 GOTO err_handle
RETURN 0
/* ... */
err_handle:
RAISERROR ('An error occurred in the stored procedure. The transaction has been rolled 
back', 12, 1)
ROLLBACK TRANSACTION
RETURN -101

WAITFOR

You can use WAITFOR to suspend execution for a specified delay (WAITFOR DELAY) or until a specified time (WAITFOR TIME). Say, for example, you want to generate a deadlock. (For instance, you might have defined an alert for a deadlock error and you want to test it.) You must be able to start execution of both batches more or less simultaneously for the deadlock to occur. This is hard to do in real time, so you can introduce a wait for 10 seconds in the first transaction to give you time to initiate the second transaction.

To try this out yourself, open up two separate connections to the same SQL Server in Query Analyzer. In one connection, execute the following code to initiate one transaction:

use pubs 
go
BEGIN TRAN
UPDATE authors SET au_lname = au_lname
WAITFOR DELAY '00:00:10'
UPDATE titles SET title = title
ROLLBACK TRAN

Quickly switch to the other connection, and execute the following code to initiate another transaction:

use pubs 
go
BEGIN TRAN
UPDATE titles SET title = title
UPDATE authors SET au_lname = au_lname
ROLLBACK TRAN

If you time it correctly, this should lead to a deadlock once the WAITFOR DELAY expires in the first connection and the second update statement is executed.

EXECUTE

The EXEC (or EXECUTE) command is used as a keyword for executing stored procedures. Introduced in version 6.0, EXEC also gives you the ability to execute strings and variables containing strings, which can be useful.

Perhaps you want to write a procedure to perform UPDATE STATISTICS for all tables in the database without having to hardcode the table names; that way, if you add or remove tables from the database, you don't have to edit the procedure. Unfortunately, the UPDATE STATISTICS command does not accept a variable as an argument. However, you can build the command dynamically and execute the command using the EXEC statement, as shown in Listing 26.14.

Listing 26.14 Dynamic SQL Execution Using the EXEC Statement

DECLARE c1 cursor
   for select table_name from information_schema.tables
    where TABLE_TYPE = 'Base Table'
declare @tbl_name NVARCHAR(128)
open c1
fetch c1 into @tbl_name
while @@fetch_status = 0
begin
    EXEC('UPDATE STATISTICS ' + @tbl_name)
    fetch c1 into @tbl_name
end
close c1
deallocate c1

Another example is if you want to write a stored procedure that will SELECT rows from a table name passed to it as an argument. SQL Server does not accept variables for table names, column names, and so on, so the following syntax will produce an error message:

SELECT * FROM @tbl_name 

To dynamically retrieve data from a table name passed in as a parameter, you can build a dynamic query to be executed by the EXEC command:

CREATE PROC general_select @tbl_name NVARCHAR(128) AS 
    EXEC('SELECT * FROM ' + @tbl_name)
GO
EXEC general_select authors
GO

For more information on executing dynamic SQL within stored procedures, see Chapter 28.

Batches

A batch is simply a set of commands sent to SQL Server for execution. Do not confuse the batch term as used here with traditional batch processing, in which mass modifications are performed, often at low-activity periods.

Basically, SQL Server receives a string (containing T-SQL commands) from the client application. SQL Server parses this string as a unit, searching for keywords. If a syntax error is found, none of the statements in the batch are executed, and an error message is returned to the client application.

In Query Analyzer, ISQL, and OSQL, the string GO is used to separate batches. When the tool finds the string GO, it takes all text up to the preceding GO and submits it to SQL Server for execution.

Some restrictions for batches concern what commands can be combined with other commands within a batch. Some examples follow:

• You cannot combine certain commands within a batch. Most CREATE commands must be executed in a single batch. The exceptions are CREATE TABLE, CREATE INDEX, and CREATE DATABASE.

• When calling a stored procedure, you must precede the procedure name with EXECUTE if it's not the first statement in a batch. If SQL Server doesn't recognize the first statement in a batch, it simply assumes that the string is a call to a stored procedure.

A related concept is the SQL script. A SQL script is a text file containing one or more batches. Scripts are often used with Query Analyzer, ISQL, and OSQL. You do not have to specify GO after the last command in a script file; the tools will automatically generate an end-of-batch signal.

Listing 26.15 creates a table and a view. Note that the CREATE commands are separated by GO.

Listing 26.15 Creating a Table and a View That Only Display Recent Orders

CREATE TABLE orders
   (order_number UNIQUEIDENTIFIER DEFAULT NEWID()  PRIMARY KEY NOT NULL,
    stor_id char(4) REFERENCES stores(stor_id),
    order_date DATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
GO
CREATE VIEW recent_orders AS
   SELECT order_number, stor_id, order_date
      FROM orders
      WHERE order_date > GETDATE() ? 14
GO

Comments

Anyone who has ever had to review or change some code recognizes the importance of comments. Even if it seems obvious what the code does when you're writing it, the meaning will most certainly not be as obvious later, especially if someone else other than the original author is looking at it.

When SQL Server finds a comment, it does not execute anything until the end of the comment. The Query Analyzer's syntax coloring indicates commented text with a green color by default. SQL Server supports two types of comment markers:

/* Comments */ 

These comment markers are useful for commenting several lines. None of the text between the comment markers is parsed, compiled, or executed. For shorter comments, you can use

-- Comments 

SQL Server will not execute any of the text following the markers up to the end-of-line. The -- comment markers are defined in ANSI SQL-92.

Following is an example of a batch with an opening comment block that describes what the batch performs and a comment line later in the code that can be altered for debugging purposes:

/* Retrieves all orders that have been submitted the last day. 
The SELECT COUNT is only for debugging purposes */
SELECT order_number, stor_id, order_date
   FROM orders
   WHERE order_date > GETDATE() -1
--SELECT 'Number of orders returned':, @@ROWCOUNT

Both types of comments can be nested within a /*...*/ comment block. Comment blocks are local to a batch and cannot span across multiple batches?in other words, you cannot specify the end-of-batch (GO) separator within a /*...*/ comment block. The example in Listing 26.16 attempts to use a comment block to comment out the GO command. Notice the error messages generated.

Listing 26.16 Attempting to Comment Out the GO Command

/* this is a comment
select * from titles
go
select * from publishers
*/
go

Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '*'.

Local Variables

Local variables allow you to store values temporarily. The variable is always declared as a certain datatype with the DECLARE statement. The datatype can either be system supplied or user defined. The variable's name always begins with the @ sign and can be up to 128 characters in length.

Local variables initially are set to NULL. The variable is assigned a value with the SELECT statement or the SET statement. The SET statement can only set one variable at a time. A SELECT statement can assign values to one or more variables at a time and it is more efficient to perform multiple assignments in single select rather than performing multiple SELECT or SET statements. Also, a SELECT statement used to assign values to one or more local variables, referred to as an assignment select, cannot also return result rows.

Listing 26.17 prints the number of distinct book types in the titles table. You declare a local variable, assign it a value, and then print the contents of the variable.

Listing 26.17 Assigning a Value to a Local Variable and Printing Its Contents

DECLARE @user_msg VARCHAR(255)
SELECT @user_msg = 'There are ' + CONVERT(VARCHAR(3),
       (SELECT COUNT(DISTINCT type) FROM titles))
        + ' book types in the titles table.'
PRINT @user_msg
go

There are 6 book types in the titles table.

Local variables, except for table variables, are scalar variables and can only hold a single value at a time. If an assignment select statement returns multiple rows, the local variable will contain the value from the last row returned. If the assignment select returns no rows, the local variable is not set to NULL, but it retains the value it had prior to the assignment select.

Local variables are often used in stored procedures.

Functions That Used to Be Called Global Variables

A certain set of functions used to be called global variables in earlier releases of SQL Server. The name global was apparently confusing to some, implying that the scope of the variable was beyond that of a local variable. Global variables were apparently mistaken by some as variables that a user could declare and the scope of the variable would extend across batches, which is not the case. You can name a variable starting with two or more at signs (@@), but it will still behave as a local variable.

These global variables, now called functions, contain information that SQL Server maintains. They exist so that an application can check things such as the error code for the last executed command. Microsoft online books categorizes functions in the various categories of the SQL Server functions. However, the documentation somewhat blurs the lines between session-level and server-level functions the way that Microsoft groups them. For this reason, this chapter categorizes them as connection-specific, monitoring-related, and general functions.

These functions are useful because some of them contain information that cannot be found elsewhere or would be difficult to obtain with other means.

For the connection-specific functions outlined in Table 26.14, SQL Server maintains separate values for each connection.

The following code fragment demonstrates using the @@ERROR function to check for errors after each statement and branching to an error-handling routine if an error occurs:

BEGIN TRAN 
INSERT orders(customer_number) VALUES(1)
 IF @@ERROR <> 0 GOTO err_handle
RETURN 0
/* ... */
err_handle:
RAISERROR ('An error occurred in the stored procedure.
The transaction has been rolled back', 12, 1)
ROLLBACK TRANSACTION
RETURN -101

Listing 26.18 demonstrates using the @@IDENTITY function. In this example you need to find out the identity value generated by the insert into customers so it can be used in the insert into the orders table. You can use the @@IDENTITY function to capture this. Note that you need to save the value returned from @@IDENTITY into a local variable if you need access to it after any subsequent INSERT statements. All INSERT statements update @@IDENTITY, even those that insert into a table without an identity column (if there is no identity column on the table, the insert will set @@IDENTITY to NULL). You do not have to worry about other connections' inserts affecting the @@IDENTITY value because @@IDENTITY is maintained per connection.

Listing 26.18 Using the @@IDENTITY Function to Get the Last Generated Identity Value

CREATE TABLE customers
  (customer_id INT IDENTITY PRIMARY KEY NOT NULL,
   customer_name NVARCHAR(100) NOT NULL,
   customer_comments NVARCHAR(1000) NULL)
Go

CREATE TABLE orders
  (order_number UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY NOT NULL,
   customer_number INT REFERENCES customers(customer_id),
   order_date DATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
GO

DECLARE @cust_id INT
INSERT customers (customer_name, customer_comments)
    VALUES ('Hardware Suppliers AB', 'Stephanie is contact.')
SELECT @cust_id = @@IDENTITY
INSERT orders (customer_number)
    VALUES (@cust_id)
go

The monitoring-related functions are listed in Table 26.15. These functions are rarely used and are included here for completeness. Typically, DBCC SQLPERF and SQL Performance Monitor give similar information in a more useful fashion.

Outlined in Table 26.16, the configuration functions provide information about the current configuration option settings, which are useful for administrative purposes. The most useful one is @@VERSION, which returns the version number and the service pack level.

Listing 26.19 shows how you can use @@VERSION to check the version number of the SQL Server. The @@VERSION function provides the SQL Server version along with the build number and code freeze date of the currently running SQL Server executable. The last three digits after the second decimal point tell the build number, which indicates which service pack you are running. Unfortunately, no standard associates build numbers with service packs. (The service pack number listed on the last line is the service pack of the operating system you are running.) The only way to know which service pack of SQL Server you are running is to check the ReadMe file that comes with the service packs. The following are the current build numbers for SQL Server 2000:

• 194?Initial Release version of SQL Server 2000

• 384?Service Pack 1

• 534?Service Pack 2

Listing 26.19 Using the @@VERSION Function to Determine the Version of SQL Server

SELECT @@VERSION
go

Microsoft SQL Server  2000 - 8.00.534 (Intel X86)
        Nov 19 2001 13:23:50
        Copyright (c) 1988-2000 Microsoft Corporation
        Developer Edition on Windows NT 5.1 (Build 2600: )

Another way of determining the version of SQL Server is to use the new SERVERPROPERTY() system function and check the productversion or productlevel server properties:

select cast (serverproperty('productversion') as varchar(20)) AS 'Product Version' 
select cast (serverproperty('productlevel') as varchar(10)) AS 'Product Level'
go

Product Version
--------------------
8.00.534

Product Level
-------------
SP2

The advantage of using the new SERVERPROPERTY() system function is obvious?no more looking up the build number in the ReadMe file to determine the service pack applied!

Returning Messages from T-SQL

SQL Server provides two methods of returning messages from within Transact-SQL code: PRINT and RAISERROR. The way that messages are handled and displayed by a client application is determined by the database API that the application uses. For example, DB-Library registers two callback handlers: an error handler and a message handler. The message handler is called when messages are sent via the PRINT statement. The error handler is called when messages are sent via the RAISERROR command. For ODBC applications, the SQLError function handles the results of PRINT and RAISERROR.

PRINT

The PRINT statement is used to pass a message string to the client program. The message string can be a fixed string, local variable or function, or any string expression up to 8,000 characters in length. Anything beyond 8,000 characters is truncated. The syntax is as follows:

print {'character_string' | @local_variable | @@function | string_expr} 

The following example displays the current time from SQL Server:

print 'The current time is: ' + convert(char(8), getdate(), 108) 

Messages returned by the PRINT command return a severity of 0. PRINT is commonly used in SQL script files to print information to the output file.

RAISERROR

Although SQL Server generates most error messages automatically, sometimes you will need to communicate to a client that some type of error has occurred within a SQL batch, trigger, or stored procedure. You can generate a message with the RAISERROR command. The RAISERROR command has the following syntax:

RAISERROR([err_no]|[err_string], severity, state [, argument[, ...]]) 
           [WITH option[, ...]]

If you supply an error string, the error number will always be 50,000. If you supply an error number, that error number and corresponding message must be defined in the sysmessages table in the master database.

The severity level indicates the type of problem encountered. Typically, the higher the severity level, the more serious the error. Severity levels higher than 19 are considered fatal errors and cause the process to terminate its connection to SQL Server. Only system administrators can raise a severity level of 19 or higher and they must specify the WITH LOG option. Typically, user-generated error messages will use only levels 10 or 16.

RAISERROR ('Help, I've fallen and I can't get up', 16, 1) 
select @@error as 'Error Number'
RAISERROR ('Hello world', 10, 1)
select @@error as 'Error Number'
Server: Msg 50000, Level 16, State 1, Line 1
Help, I've fallen and I can't get up
go

Error Number
------------
       50000


Hello world
Error Number
------------
           0

Table 26.17 presents the severity levels currently defined in SQL Server.

The state parameter can be any value from 1?127. For SQL Server?generated error messages, the value of state indicates where in the SQL Server code the error came from. This information might be useful for Microsoft Product Support. For user-defined error messages, the state really has no meaning. You could use state to indicate the line in the SQL code that generated the error. Another situation in which you might find the state parameter useful is when you execute a script using ISQL or OSQL. If you execute the RAISERROR with a state of 127, the processing of the script file terminates. Suppose you have a simple batch file that executes the following:

ISQL /Usa /P /iMyBatch.SQL /n 

and the script file (MyBatch.SQL) contains the code in Listing 26.20.

Listing 26.20 Using State 127 to Terminate a Batch Processed with ISQL or OSQL

-- Exit if users connected to database.
IF (SELECT COUNT(*) FROM master..sysprocesses
    WHERE dbid = DB_ID('pubs')) > 0
RAISERROR ('Cannot proceed with batch, users connected to database.', 16, 127)
GO
-- If not, continue with whatever you want to do
SELECT au_fname, au_lname FROM pubs..authors
go

If the IF statement evaluates to true, the RAISERROR statement will terminate the processing of the script file. This is not the same result that you get from issuing a RETURN statement. The RETURN statement would have terminated the batch but executed the remaining batches in the script file.

The arguments are used to insert data (table name or other information stored in local variables) into the message string. A maximum of 20 arguments can be specified. In the message string, you need to specify placeholders where the arguments are to be substituted. The placeholders are similar to the ones used in the C language printf command and are listed in Table 26.18.

The following is an example of using RAISERROR with arguments:

declare @count int, 
       @table varchar(128)
select @table = 'titles', @count = count(*) from titles
RAISERROR ('There are %d row(s) in the %s table', 10, 1, @count, @table)
go

There are 18 row(s) in the titles table

The available options for RAISERROR include the following:

LOG? The message is sent to SQL Server's error log and NT's event log. Only a system administrator can specify this option.

NOWAIT? The message is sent directly to the client. This is useful for long-running operations to return information without waiting until the batch completes, such as to allow an application to display a status indicator.

SETERROR? The message forces the actual error number to be returned to the function @@ERROR. This is useful when the severity is lower than 11.

Managing SQL Server Error Messages

SQL Server error messages are stored in the sysmessages table in the master database. Table 26.19 describes the columns in the sysmessages table.

User-defined error messages must have an error number that is greater than 50,000. The maximum value for an error number is 2,147,483,647.

You can also add your own error messages, which can be useful for centralizing error reporting from your application. Chapter 18, "SQL Server Scheduling and Notification," describes how to add messages in Enterprise Manager. You can also manage messages with the stored procedures sp_addmessage, sp_dropmessage, and sp_altermessage. The error number must be greater than 50,000. For more information on these commands, refer to Chapter 18.

Listing 26.21 adds a user-defined message and calls it from T-SQL code.

Listing 26.21 Adding an Error Message to SQL Server and Generating the Error

sp_addmessage 50001, 16, 'The row(s) from table %s could not be deleted. There are rows 
in table %s that refer to this row. Delete those rows first.'
go

RAISERROR (50001, 16, 1, 'Titles', 'Titleauthor')
go

Server: Msg 50001, Level 16, State 42000
The row(s) from table Titles could not be deleted. There are rows in table Titleauthor 
that refer to this row. Delete those rows first.

FORMATMESSAGE

The FORMATMESSAGE function can be used to return a message from the sysmessages table. Its typical use is to substitute arguments into the message string and construct the message as it would be returned by RAISERROR. Unlike RAISERROR, which prints the message immediately, FORMATMESSAGE returns the constructed message for further processing.

The syntax for FORMATMESSAGE is as follows:

FORMATMESSAGE ( msg_number , argument [ ,...n ] ) 

The following example uses FORMATMESSAGE with the error number 50001 defined previously:

[View full width]
declare @msg varchar(8000) 
select @msg = formatmessage(50001, 'Titles', 'Titleauthor')
print @msg

The row(s) from table Titles could not be deleted. There are rows in table Titleauthor 
that refer to this row. Delete those rows first.

SET Options

You can use the SET command to alter a connection's behavior. Options set with the SET command stay active until the connection terminates. For most options, when they are set within a stored procedure, the option is set back to its connection level setting when the procedure returns.

Most SET commands take values of ON or OFF as arguments, whereas some take a specific value. Some of the SET statements do not take effect until the next batch, whereas others will be set at parsing or execution time.

The tuning-related SET parameters are generally used when analyzing and optimizing queries. They can give you information about how SQL Server executes a query and also, to some extent, control how a query is executed. The default option settings are noted by asterisks (*) in Table 26.20.

In Listing 26.22, you turn on SHOWPLAN_TEXT so that the execution plan is returned to the client.

Listing 26.22 Using SHOWPLAN_TEXT

SET SHOWPLAN_TEXT ON
GO
SELECT title, au_fname, au_lname
 FROM titles t
   JOIN titleauthor ta ON t.title_id = ta.title_id
   JOIN authors a ON ta.au_id = a.au_id
go

StmtText
-------------------------------------------------------------------------------
SELECT title, au_fname, au_lname
 FROM titles t
   JOIN titleauthor ta ON t.title_id = ta.title_id
   JOIN authors a ON ta.au_id = a.au_id

(1 row(s) affected)

StmtText
-------------------------------------------------------------------------------
  |--Nested Loops(Inner Join, OUTER REFERENCES:([ta].[au_id]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[title_id]))
       |    |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [t]))
       |    |--Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS
[ta]), SEEK:([ta].[title_id]=[t].[title_id]) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]
AS [a]), SEEK:([a].[au_id]=[ta].[au_id]) ORDERED FORWARD)

For information on how to interpret the SHOWPLAN_TEXT or STATISTICS PROFILE information, see Chapter 36, "Query Analysis."

With the transaction-handling?related SET parameters, you can override SQL Server's default transaction-handling behavior. In Table 26.21, the default option settings are noted by asterisks (*).

In Listing 26.23, you turn on IMPLICIT_TRANSACTIONS, issue two DELETE statements, print the nesting level, and perform a ROLLBACK.

Listing 26.23 Setting IMPLICIT_TRANSACTIONS to Get an Implicit BEGIN TRANSACTION

SET IMPLICIT_TRANSACTIONS ON
GO
DELETE FROM titleauthor WHERE title_id = 'BU1032'
DELETE FROM titles WHERE title_id = 'BU1032'
print 'Transaction nesting level is: ' + CAST(@@TRANCOUNT AS VARCHAR(5))
ROLLBACK TRAN
print 'Transaction nesting level is now: ' + CAST(@@TRANCOUNT AS VARCHAR(5))
go

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
 'FK__sales__title_id__0BC6C43E'. The conflict occurr