Difference between Global Temporary Tables and Local Temporary Tables

In general, is there a performance difference between using table variables and using temporary tables in SQL Server 2000?

There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.

A senior member of the SQL Server development team told me that table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query. He also said that SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable. The development team member added that you should limit your use of SQL Server 2000 table variables to reasonably small queries and data sets and use temporary tables for larger data sets.

This advice about table variables contradicts some past Microsoft information, which said that table variables were faster because they're created in memory. However, table variables can incur disk I/O in ways similar to temporary tables.

In deciding whether you should use table variables or temporary tables, benchmark both in your environment. I suspect that temporary tables will provide better performance on larger result sets in most cases. For more information about table variables, see the Microsoft article "INF: Frequently Asked Questions—SQL Server 2000—Table Variables".

A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost. Multiple users can't share a local temporary table because it is local to one user session. You also can't grant or revoke permissions on the local temporary table.

A global temporary table, ##table_name, also exists for the duration of a user session or the procedure that created the table. When the last user session that references the table disconnects, the global temporary table is lost. However, multiple users can access a global temporary table; in fact, all other database users can access it. But you can't grant or revoke permissions on the global temporary table because it's always available to everyone.

Local and global temporary tables differ in a subtle way. Let's look at what SQL ServerBooks Online (BOL) says about temporary tables. "Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use. The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their lifetimes." The local table we created in our procedure by using sp_executesql won't be accessible to either the procedure or its child procedures. Within its execution scope, an sp_executesql system stored procedure creates and drops a local table. By definition, when the sp_executesql procedure ends, the life of the table also ends—hence the need to create a global temporary table.

I listened intently to the new Oracle programmer as he described all the struggles he's been having on his first big project. As I've done many times already in his short career, I interrupt with some words of wisdom.

"It's time to add Global Temporary Tables to your toolbelt."

"What are those?" he asks, as he opens the directory with the Oracle documentation. I smile. He has already learned where I always send him first.

"They're the ultimate work tables," I continue. "They're permanent tables, where you can add and modify session-specific data without affecting other sessions."

"What's so special about that?" he asks. "Even with regular tables, you can add and modify the data all you want without affecting other sessions. Just don't commit, and remember to rollback when your session is done."

"Oh yeah? And what about all the persistent work you're doing in your session? How do you commit that?"

"Oh yeah. Does it allow for indexes, and triggers, and views with regular tables?"

"Yep, all of that. See for yourself, it's easy. You've got the manual in front of you, so you drive."

Then I watched as he opened one session, and created a global temporary table.

SQL> CREATE GLOBAL TEMPORARY TABLE worktable (x NUMBER(3));

Table created.

He opened another session and was pleased to see the table there. He then added a row in the first session, committed it, and was planning to use the other session to see if the data was there. But instead he was in for a little surprise.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

        X
----------
        1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

no rows selected

"Hey!" he shouted. Heads popped up in nearby cubicles. "Where did it go?"

"Keep reading," I said, gesturing towards the "ON COMMIT" options for Global Temporary Tables. "By default, every time you commit your data, it is assumed that you want to clear out your work tables. Try PRESERVE."

He dropped the table, and tried again.

SQL> CREATE GLOBAL TEMPORARY TABLE worktable
 2  (x NUMBER(3))
 3  ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

        X
----------
        1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

        X
----------
        1

"That's better," he smiled. Now let's check the other session.

SQL> SELECT * FROM worktable;

no rows selected

"Excellent. So this data will remain there until my session ends?"

"Yep. Try it." He exited his session, logged back in, and sure enough the data was gone. "This is great. But what if I want to get rid of the data at some point in my session?"

"Truncate. Truncating the work table will only truncate the data in your session, not all the data."

"Hey neat. Thanks, this will be very useful. What are you doing for lunch later?"

"Aren't you even going to try it? What, you're just going to trust me?" I said. I think he was a little surprised that I would I'd rather talk about work tables than lunch. Frankly, so am I. Thankfully, he worked quickly, typing first in his second session.

SQL> INSERT INTO worktable (x) VALUES (2)

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

        X
----------
        2

SQL> TRUNCATE TABLE worktable;

Table truncated.

SQL> SELECT * FROM worktable;

no rows selected

SQL> commit;

Commit complete.

"I don't think you need all those commits," I laughed. "But ok, now look back in your first session. If the row you added previously isn't there, then I'm a big fat liar. Otherwise, we can talk about lunch."

SQL> SELECT * FROM worktable;

        X
----------
        1

Steps to have an Automatically Approved Purchase Orders Created from Requisitions

1. Identify the Supplier and Supplier Site that is desired to have the PO created automatically for.

2. Create a Quotation (Set it to Approved Status) for each item used by the selected Supplier and Supplier Site.

3. Create an ASL for the item and add the Supplier and Supplier Site.

4. Under the ASL Attributes, add the Quotation entered in Step 2

5. Create a Sourcing Rule for the Supplier and Supplier Site

6. Create an Assignment Set for this Organization or Item and list this Sourcing Rule in that Assignment Set
    (One Assignment Set can be used that will include all of the desired Assignments)

7. Set the Profile - MRP: Default Sourcing Assignment Set to the Assignment Set created in Step 6

8. Set the Workflow Item Attributes

- Is Automatic Creation Allowed? = Yes    (This is the PO Create Documents workflow)
- Is Automatic Approval Allowed? = Yes     (This is the PO Create Documents workflow)
- Send PO Autocreation to Background? = No    (This is the Requisition approval workflow)

To update the attributes, follow these steps in workflow builder:

a. Open workflow builder and connect to the database as the APPS user. Open the PO Create Workflow. The display name is PO Create Documents.

b. Expand the data source, and then the PO Create workflow item type branch within that data source.

c. Expand the Attributes branch within the PO Create workflow branch.

d. Double click on the attribute to open the Properties window. Set the value of the attributes as indicated.

e. Repeat the steps for item type PO Requisition Approval to set the indicated attribute.

9. Create a Requisition and submit it for Approval

10. Once Approved the Create Documents workflow will kick off and create a Standard PO

11. Since Is Automatic Approval Allowed? = Yes, the PO will be automatically submitted for Approval

12. If the Buyer defaulted on the PO has the approval authority the PO will be approved.
      If not, the PO will be forwarded up the Buyer's Approval Hierarchy that is already setup.
      The hierarchy used in the Create Documents Workflow, by which the Buyer is determined is as follows: 
      (Will start with the Step 1, and continue until either a Buyer is found, or the hierarchy is exhausted.)

1. Get Buyer from Requisition Line
2. Get Buyer from Item
3. Get Buyer from Category on Requisition
4. Get Buyer from Source Document (For this case the Quotation created in Step 2 above)
5. Get Buyer from Contract (The Contract would need to exist for the Supplier and Supplier site)

NOTE: In R12 and higher this logic has changed where we decide if OK to use buyer from source document.
-If OK to use buyer from source document depends on the HR: Security Profile setting and if the source document is a global agreement.
-If the source document is a CPA, it will not be considered.