There is a feature of SQL I have not seen used much and probably with good reason. It’s the use of temporary tables. These are declared with the expected DDL. Since they were relatively easy to add to the language, they have been around for a while. The bad news is that in the early days SQL programmers tended to write code that looked more like a procedural language.
They mimicked how you would hang a scratch tape and use it to either hold complicated intermediate results or to hold final results from a procedure that you wanted to pass on to another application in the presentation layer of a tiered architecture. Scratch tapes were popular because main storage (usually magnetic core, not yet semiconductors) wasn’t big enough to hold a lot of data.
You had to offload it to some media that could hold bulk data. That first meant magnetic tapes. Later, disk storage could hold enough data that you didn’t need mag tapes anymore. But the mental model of materialized and stored intermediate results was still with us.
(Editor’s note: I have seen them used in SQL Server regularly in the ways being discussed, and not in the distant past.)
Since the addition of Common Table Expressions (CTE) to SQL, temporary tables can often be avoided. As a generalization, it’s better to put everything you can in one SQL statement and trust in the optimizer to do a better job than a human can.
The full syntax for the CREATE TABLE
, with all the clauses, is:
1 2 3 |
CREATE [GLOBAL | | LOCAL] TEMP[ORARY] TABLE <table name> <table element list> ON COMMIT [ PRESERVE] | [DELETE] ROWS; |
A temporary table is created and not declared in SQL. This is an important difference. The SQL verb CREATE
means the schema object is persistent; if we want an object that disappears at the end the session then we use DECLARE
. This is a little bit confusing in that these temporary tables sound like they should be, well, temporary.
It’s actually not the table that’s temporary, but the rows in the table that will be flushed out by SQL at the end of the session. Perhaps you notice that we DECLARE CURSOR
in SQL and local variables in SQL/PSM for this reason. When you DROP
a <schema object>, then it disappears from the database.
Implementation Discussion
While the basic syntax will be pretty much the same across products, the underlying physical implementations will vary quite a bit. In this section I will cover a few examples.
SQL Server Implementation
SQL Server users need to use a #
or ##
prefix on the table name, instead of the ANSI/ISO standard keywords. A single octothorpe prefixes a local temporary table, and the double octothorpe marks a global temporary table. Octothorpe is a word you might only see a crossword puzzle or a typography book, but it is the proper name for the “pound sign”, “hash mark”, “ordinal number sign” or whatever else it is being called these days.
As such, you may create a temporary table using the following example syntax:
1 2 3 4 |
CREATE TABLE #TempResults ( <Columns Specification> …. |
All you need is the one octothorpe to make it a local temporary table or change it to CREATE TABLE ##TempResults
for a global one.
Temporary tables support all DDL and DML operations, just like regular base tables. This means that you can INSERT
, UPDATE
, DELETE
, and SELECT
data, as well as create indexes, constraints, and apply modifications to the table structure.
A downside of temporary tables in SQL Server is that they are created in the tempdb
database, shared by all SQL Server users. This can cause resource contention and performance issues on busy servers. Remember that tempdb
gets used a lot of different things in SQL Server.
A global temporary table is visible to all active sessions and connections. These objects remain accessible until the last connection using it terminates. This can be used to give you an “ephemeral dropbox” among the users to share values. In practice there are few uses for global temporary tables.
While SQL Server cleans out the temporary tables, the ANSI/ISO version of this feature allows different actions when you finally commit the work. The ON COMMIT [ PRESERVE] | [ DELETE] ROWS
clause can keep (preserve) the rows of this table when you commit, or it can flush them out, so they never show up in the committed work.
As an aside, the use of @ and @@ prefixes on parameter and table variable names and # and ## prefixes on local and global temporary tables is a convention inherited from the old Sybase days. If you were a BASIC programmer in the early days, you may remember the use of the $ prefix to identify string variables in the early versions of that language. But even before these non-alphanumeric prefixes, the original FORTRAN could tell integers from floating-point numbers by their first letter (I thru N for integers). This language design makes the parser easier because does not require building a more complicated symbol table in the compiler.
SQL Server has another method to load data into a temporary table (or a permanent table, for that matter). Let me just give you the syntax first and then a discussion:
1 2 3 4 |
SELECT <column list> INTO #<temporary_table name> FROM <table_name> [WHERE <search condition>]; |
In Standard SQL, the SELECT INTO
syntax is called a “singleton select” and it’s not used much. It does a select in the usual manner, but it must return only a single row into local variables. I don’t know how this feature got into the Standards, but it did.
The SQL Server statement returns a complete query result. You can also use aliases to rename the columns, do computations and apply search conditions. It’s just a regular query. Please note that unlike the usual INSERT INTO
syntax, the target table does not have to already exist. But you don’t get to change the data types, create indexes, or do any of the other stuff that you can with a regular insertion statement. Think of this as a quick “note pad” way of materializing your raw data.
Oracle’s Implementation
A variation of temporary tables was introduced in Oracle version 18c called a private temporary table. A private temporary table is a memory-based table that is dropped at the end of the session or transaction depending on the setup. The data and structures are only accessible by the session that creates them.
The syntax you use to create them has an interesting wrinkle; in that you can specify persistence of the data after a COMMIT
. As an example, the following object will be private, and the table will cease to exist on commit (the ora$ptt_
prefix is defined in the PRIVATE_TEMP_TABLE_PREFIX
initialization parameter, which defaults to ora$ptt_
. You can see more here):
1 2 3 4 |
CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_results ( <column specification> ) ON COMMIT DROP DEFINITION; |
When the transaction is committed, the structure will be preserved. Change the ON clause to ON COMMIT PRESERVE DEFINITION
; if you want the data to exist after commit until the end of the session.
Since 8c Oracle has had a Global Temporary Table as well. The name is a bit confusing if you have used any other temporary tables. The structure is permanent, but the data is temporary. Unlike SQL Server, data is not shared, just the structure of the table is.
Global temp tables have similar settings in terms of data preservation, but the definition is always preserved. You can delete the rows on commit with: ON COMMIT PRESERVE ROWS;
and remove your rows using ON COMMIT DELETE ROWS;
Much like SQL Server’s SELECT INTO
structure, Oracle allows you to use what is called CTAS (Create Table As SELECT
). So you can create a temporary table using:
1 2 |
CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_results AS SELECT … |
For more details, you can check out the Oracle documentation here.
PostgreSQL’s Implementation
PostgreSQL has the concept of LOCAL TEMPORARY TABLE
but not a GLOBAL TEMPORARY TABLE
. The temporary table must be created by each session that uses it and the lifetime of the rows are either until COMMIT
or session termination. PostgreSQL has also an additional clause ON COMMIT DROP
that automatically drops the temporary table at the end of a transaction. The temporary table is always dropped when the session ends.
The GLOBAL
keyword is obsolete in PostgreSQL (and has the same effect as LOCAL
), so it will generate a warning about the use of the deprecated keyword that, you can ignore. If the TRUNCATE
statement is issued against a temporary table, only the session specific data is truncated. There is no effect on the data of other sessions.
Data in temporary tables is automatically deleted at the end of the database session, even if session ends abnormally. Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session. Views can be created against combinations of temporary and permanent tables.
Temporary tables can have triggers associated with them. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed. Statistics on temporary tables are common to all sessions. There are several restrictions related to temporary tables, but these are version specific.
Much like Oracle, you can specify an ON COMMIT
action of either PRESERVE ROWS
, DELETE ROWS
, or DROP
for your temporary tables when the transaction is committed.
For more settings on tables in PostgreSQL, including temporary tables, check the documentation here.
Stored Procedures and Temporary Tables
Some products allow stored procedures in their procedural extensions to create temporary tables that are local to the stored procedure. Other versions may only allow stored procedure to access a global temporary table.
You need to look up how your particular product works in more detail to get all of the . And you probably need to experiment with it a little bit. For example, are local temporary tables created for each invocation of the procedure? Is the scope of the temporary table limited to the stored procedure or is it global? How does the scoping of temporary tables work, when I have nested stored procedures? Do not assume that every product works the same way, nor that each version of a particular product will follow the same rules as prior version.
If some readers have experiences with various versions of various products, please drop us a line and will try to get a list of these various implementations.
Load comments