Transact-SQL Tips

Database programmers sometimes encounter puzzling query issues that other programmers may already have resolved. This section lists some of the common challenges and guidelines that improve query performance.

• Cross-Tab Reports. Creates summary information. Most cross-tab reports can be generated by using either the CASE function or the CUBE or ROLLUP options of SELECT.
  
  
• Expanding Hierarchies. Shows the hierarchical steps of getting to a particular result (for example, tracing a genealogical family tree from a great-great-grandparent to yourself).
  
  
• Expanding Networks. Shows the multiple hierarchical steps (for example, all flights originating from Seattle and landing in New York).
  
  
• Writing International Transact-SQL Statements. Lists guidelines for writing applications that can be adapted for use around the world.
  
  
• Writing Readable Code Lists good programming practices that make code usable, flexible, and understandable.

Transact-SQL Programming for Improved Performance

When writing Transact-SQL statements, batches, stored procedures, and triggers, use the programming features in Microsoft® SQL Server™ 2000 to create efficient code.

Reusing Execution Plans

SQL Server 2000 has a better chance of reusing execution plans of Transact-SQL statements if they are written following these guidelines.

• Use fully qualified names of objects such as tables and views.
  
  
• Use parameterized queries, and supply the parameter values instead of specifying stored procedure parameter values or the values in search condition predicates directly. Use either the parameter substitution in sp_executesql or the parameter binding of the ADO, OLE DB, ODBC, and DB-Library APIs.

  For example, do not code this SELECT:

  SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3
  

  Instead, using ODBC as an example, use the SQLBindParameter ODBC function to bind the parameter marker (?) to a program variable and code the SELECT statement as:

  SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = ?
  

  In a Transact-SQL script, stored procedure, or trigger, use sp_executesql to execute the SELECT statement:

  DECLARE @IntVariable INT
  DECLARE @SQLString NVARCHAR(500)
  DECLARE @ParmDefinition NVARCHAR(500)
  
  /* Build the SQL string. */
  SET @SQLString =
       N'SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = @ShipID'
  /* Specify the parameter format once. */
  SET @ParmDefinition = N'@ShipID int'
  
  /* Execute the string. */
  SET @IntVariable = 3
  EXECUTE sp_executesql @SQLString, @ParmDefinition,
                        @ShipID = @IntVariable
  

• Use sp_executesql when you do not need the overhead of defining stored procedures. Always use sp_executesql instead of a temporary stored procedure.

For more information, see Execution Plan Caching and Reuse and Building Statements at Run Time.

Reusing Execution Plans for Batches

When multiple concurrent applications will be executing the same batch with a known set of parameters, implement the batch as a stored procedure that will be called by the applications.

When an ADO, OLE DB, or ODBC application will be executing the same batch multiple times, use the PREPARE/EXECUTE model of executing the batch. Use parameter markers bound to program variables to supply all needed input values, such as the expressions used in an UPDATE VALUES clause or in the predicates in a search condition.

Using the ODBC Escape Sequence

When calling a stored procedure from an ADO, OLE DB, or ODBC application, use the ODBC { CALL procedure_name } escape sequence instead of the Transact-SQL EXECUTE statement. For more information, see Calling a Stored Procedure.

Outstanding Transactions and Result Sets

Do not keep a transaction outstanding for long periods of time. A long-standing transaction can reduce throughput by holding locks on rows for long times, preventing other connections from accessing the rows in a timely manner.

Do not keep a result set outstanding for a long period of time. After executing a Transact-SQL batch, fully process or cancel all result sets from the batch as quickly as possible.

Minimizing Rows and Operations

Minimize the number of rows returned from a SELECT statement by using the WHERE and HAVING clauses to select only the rows needed.

Minimize the use of not equal operations, <>, or !=. SQL Server has to scan a table or index to find all values to see if they are not equal to the value given in the expression. Try rephrasing the expression using ranges:

WHERE KeyColumn < 'TestValue' AND KeyColumn > 'TestValue'

Reduce roundtrips between the application and the server by:

• Including multiple statements in a single batch sent from the application to the server. For more information, see Batches.
  
  
• Placing several Transact-SQL statements in a single stored procedure. This reduces the amount of information that has to be sent from the application.
  
  
• Reserving the use of server cursors to when the cursor functionality is needed by the application; use a default result set instead. For more information, see Cursors.

  For ODBC applications, consider using a fast forward-only cursor with the autofetch option. For more information, see Fast Forward-Only Cursors (ODBC).

Using Advanced Features

Use advanced features available in Transact-SQL to perform work in one batch on the server instead of pulling the results to the application and then using them to send another Transact-SQL statement to SQL Server:

• Use variables and control-of-flow statements to build logic into batches, stored procedures, and triggers instead of pulling large result sets to the client and performing the logic there. For more information, see Using Multiple Statements.
  
  
• Use constructs, such as CASE, to include logic in individual Transact-SQL statements. For more information, see Using CASE.
  
  
• Use the UPDATE statement with the FROM clause to update values in one table using values from other tables in one operation instead of selecting the source result set to the client and then updating the target table one row at a time.
  
  
• Use the join capabilities of SQL Server 2000. For more information, see Join Fundamentals.

Keeping Data Definition Language Statements Together

Within a batch, keep all data definition language (DDL) statements for a temporary table together. For example:

/* Example 1. */
CREATE TABLE #temp1 (ColA INT NOT NULL)
CREATE UNIQUE INDEX MyIndex ON #temp1(ColA)
INSERT INTO #temp1 SELECT IntCol FROM SomeTable
SELECT * FROM #temp1
GO

/* Example 2. */
CREATE TABLE #temp1 (ColA INT UNIQUE NOT NULL)
INSERT INTO #temp1 SELECT IntCol FROM SomeTable
SELECT * FROM #temp1
GO

Do not code:
/* Example 3. */
CREATE TABLE #temp1 (ColA INT NOT NULL)
INSERT INTO #temp1 SELECT IntCol FROM SomeTable
CREATE UNIQUE INDEX MyIndex ON #temp1(ColA)
SELECT * FROM #temp1
GO

Each time a DDL operation is performed on a temporary table, all batches that refer to it must be recompiled. The query optimizer ensures that the CREATE statements in Examples 1 and 2 are done in one operation and the batches are recompiled only once. In Example 3, the INSERT statement between the CREATE statements forces a separate recompile for each CREATE statement.

Minimizing the Use of Temporary Tables

Minimize the use of temporary tables as places to store intermediate results in a series of Transact-SQL statements. Some logic is too complex to perform in a single Transact-SQL statement. In these cases, you must code multiple Transact-SQL statements and use temporary tables to pass the results of one statement to the next. Creating and maintaining the temporary tables requires overhead; if possible, consider coding the operation as a single, more complex Transact-SQL statement.

In SQL Server 2000, use of temporary tables in stored procedures and triggers may cause the stored procedure or trigger to be recompiled every time it is used. To avoid such recompilation, stored procedures or triggers that use temporary tables must meet the following requirements:

• In the stored procedure or trigger, all statements that contain the name of a temporary table must refer to a temporary table created in the same stored procedure. The temporary table cannot have been created in a calling or called stored procedure, or in a string executed using EXECUTE or sp_executesql.
  
  
• All statements that contain the name of a temporary table must appear syntactically after its creation in the stored procedure or trigger.
  
  
• The stored procedure or trigger cannot contain any DECLARE CURSOR statement whose SELECT statement references a temporary table.
  
  
• All statements that contain the name of any temporary table must precede any DROP TABLE statement that references a temporary table. DROP TABLE statements are not needed for temporary tables created in a stored procedure; the tables are dropped automatically when the procedure terminates.
  
  
• Statements creating a temporary table (such as CREATE TABLE or SELECT INTO) may not appear in a control-of-flow statement such as IF...ELSE or WHILE.

Preventing Issues with Dates

To prevent issues with the interpretation of centuries in dates, do not specify years using two digits. For example:

/* Do this. */
SELECT *
FROM Northwind.dbo.Orders
WHERE OrderDate > '12/31/1997'
/* Do not do this. */
SELECT *
FROM Northwind.dbo.Orders
WHERE OrderDate > '12/31/97'