Chat now with support
Chat with Support

SQL Optimizer for SQL Server 10.1.2 - User Guide

Welcome to SQL Optimizer Optimize SQL
Create Optimize SQL Sessions Open Optimize SQL Sessions Rewrite SQL Plan Control Use Temporary Tables
Optimize Indexes Find SQL Scan SQL Manage Plan Guides Configure Options Tutorials About Us

About Temporary Tables

Creating temporary tables to extract data from permanent tables is a technique you can use to improve SQL statement performance. When SQL Optimizer rewrites a SQL statement, it may use this technique to generate some of the alternatives. The following is an example of an original SQL statement rewritten to use temporary tables.

Original SQL




FROM B)                   


SQL Alternative









  • Since temporary tables are session related, you need to create temporary tables for each new session.

  • SQL Scanner uses separate SQL Server sessions than SQL Optimizer. Therefore, temporary tables you create in SQL Scanner are not available when you send a statement to SQL Optimizer.


Related Topics  

Use Temporary Tables in Scan SQL

Scan Source Code with Temp Tables

Create Temporary Tables

You can use the Temp Table Manager window to create temporary tables in SQL Optimizer or SQL Scanner. The Temp Table Manger supports the following SQL statements that create or modify temporary tables:


SQL Optimizer or SQL Scanner automatically drops the temporary tables you create using the Temp Table Manager when you quit the program or select a new connection.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To create a temporary table

  1. Click in the Alternative Details pane of SQL Optimizer or the Job List pane of SQL Scanner.

  2. Review the following for additional information:

    Temp Table Manager Description

    Indicates the scanner job associated with the temporary table.

    Note: This field is only available when you access the Temp Table Manager from SQL Scanner.

    SQL Script Editor

    Enter SQL statements to create temporary tables.

    Tip: Click to open a file with your SQL statement.

    SQL Script Displays the SQL script for temporary table you select.
    Temp Table List

    Displays a list of temporary tables for your current session.

    Tip:Click to drop all temporary tables for your current session.

    Note: If the selected SQL statement uses a variable, the Set Bind Variables window displays so you can define the variable. See Set Bind Variables for more information.


Related Topics  

About Temporary Tables

Use Temporary Tables in Scan SQL

Use Temporary Tables in Scan SQL

When SQL Scanner extracts INSERT, UPDATE, DELETE, or SELECT SQL statements, it determines if the statements access a temporary table. For statements that access a temporary table, SQL Scanner looks for a temporary table created previously to retrieve the execution plan. If a temporary table does not exist, SQL Scanner creates one automatically and uses it to retrieve the execution plan. All temporary tables are automatically dropped after the scanning process.

SQL Scanner automatically creates a temporary table for CREATE TABLE or SELECT INTO statements when you select the Create Scanner Temp Table checkbox in the Scanner Options page. If you clear this checkbox, SQL Scanner no longer creates temporary tables and classifies SQL statements that access temporary tables as invalid.See Scan SQL Options for more information.

You have the following options for how SQL Scanner handles temporary table creation:

  • SQL Scanner can use temporary tables you created using the Temp Table Manger or create new ones that override these user-defined temporary tables. Select the Override User-Defined Temp Tables checkbox in the Scanner Options page to have SQL Scanner override your previously created temporary tables.

  • SQL Scanner can use a new table definition each time it finds a new CREATE TABLE or SELECT INTO statement. Select the Override previous Scanner Temp Table checkbox in the Scanner Options page to have SQL scanner override temporary tables created previously by SQL Scanner.

Note: SQL Scanner uses the first CREATE TABLE or SELECT INTO statement it finds for each temporary table for all files and database objects associated with a scan job. If several SQL statements in a scan job use the same temporary table, SQL Scanner creates the temporary table for the first statement and uses the same table for the remaining statements.


Related Topics  

Create Temporary Tables

Scan Source Code with Temp Tables

Manage Groups

This topic may not include a description for every field in the screen or dialog. 

To manage a group

  1. Select the Scan SQL tab in the main window.

  2. Create a scan job using the Add Scanner Jobs wizard. See Scan SQL for more information.

  3. Select the Scanner node from the Task pane.


  • Right-click a group and select Rename Group to rename the group.

  • Select a group and click to delete the group.

  • You can generate a Group Summary report for a selected group. See Generate Group Summary Reports for more information.

  • Click Go to sub view in the Type of Jobs pane and select a job type to display a chart of the job type.


Related Topics  

Scan SQL

Manage Scan Jobs

View SQL Classification

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating