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
SELECT *
FROM A
WHERE A.KEY IN (SELECT B.KEY
FROM B)
SQL Alternative
SELECT DISTINCT COL1 = B.KEY
INTO #TEMP1
FROM B
SELECT *
FROM A, #TEMP1
WHERE A.KEY = #TEMP1.COL1
Notes:
-
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.
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:
- SELECT INTO
- CREATE TABLE
- CREATE INDEX
- DROP INDEX
- DROP TABLE
- INSERT
- UPDATE
- DELETE
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
-
Click
in the Alternative Details pane of SQL Optimizer or the Job List pane of SQL Scanner.
-
Review the following for additional information:
Temp Table Manager Description Job 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.
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.
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
-
Select the Scan SQL tab in the main window.
-
Create a scan job using the Add Scanner Jobs wizard. See Scan SQL for more information.
-
Select the Scanner node from the Task pane.
Tips:
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.