Lesson 4:Executing Transact-SQL Statements

3 4

SQL Server 2000 provides several methods for executing Transact-SQL statements. You can execute single statements, or you can execute the statements as a batch (which is a group of one or more Transact-SQL statements). You can also execute Transact-SQL statements through stored procedures and triggers. This lesson introduces you to the various methods that you can use to execute statements. This lesson also provides you with an overview of how these statements are executed, depending on the method of execution. Finally, this lesson discusses how you can use scripts to execute Transact-SQL statements.

---

After this lesson, you will be able to:

• Identify methods used to execute Transact-SQL statements.
• Describe how Transact-SQL statements, batches, stored procedures, and triggers are processed.
• Describe Transact-SQL scripts and how they are used.

Estimated lesson time: 25 minutes

---

Single Transact-SQL Statements

Processing a single SQL statement is the most basic way that SQL Server 2000 executes SQL statements. How a statement is processed can best be illustrated by looking at how a SELECT statement is processed.

A SELECT statement is nonprocedural; it does not state the exact steps that the database server should use to retrieve the requested data. In other words, the database server must analyze the statement to determine the most efficient way to extract the requested data. This process is called optimizing the SELECT statement, and the component that performs this procedure is called the query optimizer.

The process of choosing one execution plan from several possible plans is called optimization. The query optimizer is one of the most important components of a SQL database system. While the query optimizer uses some overhead to analyze the query and choose a plan, this overhead is compensated several-fold when the query optimizer picks an efficient execution plan. For example, two construction companies can be given identical blueprints for a house. If one company spends a few days at the start to plan how it will build the house and the other company starts building without planning, the company that takes the time to plan its project will most likely finish first.

The query optimizer is important because it enables the database server to adjust dynamically to changing conditions in the database without requiring input from a programmer or from a database administrator. This process enables programmers to focus on describing the final result of the query. They can trust that the query optimizer will always build an efficient execution plan for the state of the database each time the statement is run.

Processing a SELECT Statement

The steps used to process a single SELECT statement referencing only local base tables (no views or remote tables) illustrates the basic process of executing most Transact-SQL statements. SQL Server uses the following steps to process a single SELECT statement:

1. The parser scans the SELECT statement and breaks it into logical units, such as keywords, expressions, operators, and identifiers.
2. A query tree, sometimes called a sequence tree, is built by describing the logical steps needed to transform the source data into the format needed by the result set.
3. The query optimizer analyzes all of the ways in which the source tables can be accessed and selects the series of steps that will return the result fastest while consuming the fewest resources. The query tree is updated to record this exact series of steps, and the final, optimized version of the query tree is called the execution plan.
4. The relational engine begins executing the execution plan. As steps that need data from the base tables are processed, the relational engine uses OLE DB to request the storage engine to pass up data from the row sets that are requested from the relational engine.
5. The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

Processing Other Statements

The basic steps described for processing a SELECT statement apply to other SQL statements as well, such as INSERT, UPDATE, and DELETE. UPDATE and DELETE statements both target the set of rows to be modified or deleted. The process of identifying these rows is the same process used to identify the source rows that contribute to the result set of a SELECT statement. The UPDATE and INSERT statements can both contain embedded SELECT statements that provide the data values to be updated or inserted.

Even DDL statements such as CREATE PROCEDURE or ALTER TABLE are ultimately resolved to a series of relational operations on the system catalog tables and sometimes (such as ALTER TABLE and ADD COLUMN) against the data tables.

Batches

A batch is a group of one or more Transact-SQL statements sent all at once from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit (called an execution plan). The statements in the execution plan are then executed one at a time.

A compile error, such as a syntax error, prevents the compilation of the execution plan so that none of the statements in the batch are executed. A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:

• Most run-time errors stop the current statement and the statements that follow it in the batch.
• A few run-time errors, such as constraint violations, stop only the current statement. All of the remaining statements in the batch are executed.

The statements executed before the one that encountered the run-time error are not affected. The only exception is if the batch is in a transaction and the error causes the transaction to be rolled back. In this case, any uncommitted data modifications made before the run-time error are rolled back.

For example, assume that there are 10 statements in a batch. If the fifth statement has a syntax error, none of the statements in the batch are executed. If the batch is compiled and the second statement fails while executing, the result of the first statement is not affected (because it has already been executed).

The following rules apply to batches:

• CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must begin the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
• A table cannot be altered and then the new columns referenced in the same batch.
• If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

The GO Command

SQL Query Analyzer, the osql utility, and the isql utility use the GO command to signal the end of a batch. GO is not a Transact-SQL statement; rather, it simply signals to the utilities how many SQL statements should be included in a batch. In SQL Query Analyzer and osql, all of the Transact-SQL statements from one GO command to the next are put in the string sent to SQLExecDirect. In isql, all of the Transact-SQL statements between GO commands are placed into the command buffer before being executed.

Because a batch is compiled into a single execution plan, a batch must be logically complete. The execution plan created for one batch has no capacity to reference any variables declared in another batch. Comments must both start and end in the same batch.

The following example creates a view. Because CREATE VIEW must be the only statement in a batch, the GO commands are required to isolate the CREATE VIEW statement from the USE and SELECT statements around it.

 USE pubs GO CREATE VIEW auth_titles AS SELECT * FROM authors GO SELECT * FROM auth_titles GO 

Batch Processing

A batch is a collection of one or more SQL statements sent in one unit by the client. Each batch is compiled into a single execution plan. If the batch contains multiple SQL statements, all of the optimized steps needed to perform all of the statements are built into a single execution plan.

There are several ways to specify a batch:

• All of the SQL statements sent in a single execution unit from an application make up a single batch and generate a single execution plan.
• All of the statements in a stored procedure or trigger make up a single batch. Each stored procedure or trigger is compiled into a single execution plan.
• The string executed by an EXECUTE statement is a batch compiled into a single execution plan.
• The string executed by an sp_executesql system stored procedure is a batch compiled into a single execution plan.

If a batch sent from an application contains an EXECUTE statement, the execution plan for the executed string or stored procedure is performed separately from the execution plan containing the EXECUTE statement. The execution plan generated for the string executed by an sp_executesql stored procedure also remains separate from the execution plan for the batch containing the sp_executesql call. If a statement in a batch invokes a trigger, the trigger execution plan executes separately from the original batch.

For example, a batch that contains the following four statements uses five execution plans:

• An EXECUTE statement executing a stored procedure
• An sp_executesql call executing a string
• An EXECUTE statement executing a string
• An UPDATE statement referencing a table that has an update trigger

Figure 2.12 illustrates how these four statements are processed.

Figure 2.12  Batch processing.

Stored Procedures and Triggers

A stored procedure is a group of Transact-SQL statements that is compiled one time and that can then be executed many times. This functionality increases performance when the stored procedure is executed because the Transact-SQL statements do not have to be recompiled.

A trigger is a special type of stored procedure that a user does not call directly. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

A CREATE PROCEDURE or CREATE TRIGGER statement cannot span batches. In other words, a stored procedure or trigger is always created in a single batch and is compiled into an execution plan.

Stored Procedure and Trigger Execution

SQL Server 2000 stores only the source for stored procedures and triggers. When a stored procedure or trigger is first executed, the source is compiled into an execution plan. If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. If the plan has aged out of memory, a new plan is built. This process is similar to the process that SQL Server 2000 follows for all SQL statements. The main performance advantage that stored procedures and triggers have in SQL Server 2000 is that their SQL statements are always the same; therefore, the relational engine matches them with any existing execution plans.

Stored procedures had a more pronounced performance advantage over other SQL statements in previous versions of SQL Server. Earlier versions of SQL Server did not attempt to reuse execution plans for batches that were not stored procedures or triggers. The only way to reuse execution plans was to encode the SQL statements in stored procedures.

The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch that is calling the stored procedure or firing the trigger. This feature facilitates more frequent reuse of the stored procedure and trigger execution plans.

Stored procedures are discussed in more detail in Chapter 8, "Implementing Stored Procedures." Triggers are discussed in more detail in Chapter 9, "Implementing Triggers."

Transact-SQL Scripts

A script is a series of Transact-SQL statements stored in a file. The file can be used as input to SQL Query Analyzer or to the osql and isql utilities. The utilities then execute the SQL statements stored in the file.

Transact-SQL scripts have one or more batches. The GO command signals the end of a batch. If a Transact-SQL script does not have any GO commands, it is executed as a single batch.

You can use Transact-SQL scripts to perform the following tasks:

• Keeping a permanent copy of the steps used to create and populate the databases on your server (a backup mechanism)
• Transferring the statements from one computer to another (when necessary)
• Quickly educating new employees by enabling them to find problems in the code, to understand the code, or to change the code

Lesson Summary

SQL Server 2000 provides several methods for executing Transact-SQL statements. Processing a single SQL statement is the most basic way that SQL Server 2000 executes SQL statements. When a statement is processed, the database server analyzes the statement to determine the most efficient way to extract the requested data. The process of choosing one execution plan from several possible plans is called optimization. When processing a statement, SQL Server follows specific steps. SQL Server can process statements as a batch. A batch is a group of one or more Transact-SQL statements sent all at once from an application to SQL Server for execution. SQL Query Analyzer, the osql utility, and the isql utility use the GO command to signal the end of a batch. Each batch is compiled into a single execution plan. If the batch contains multiple SQL statements, all of the optimized steps needed to perform all of the statements are built into a single execution plan. A stored procedure is a group of Transact-SQL statements that are compiled one time and that can be executed many times. A trigger is a special type of stored procedure that a user does not call directly. SQL Server 2000 stores only the source for stored procedures and triggers. A script is a series of Transact-SQL statements stored in a file. The file can be used as input to SQL Query Analyzer or to the osql and isql utilities. The utilities then execute the SQL statements stored in the file.