SQL SERVER – Story of Temporary Objects

SQL Server’s version of Transact SQL provides the ability to create and leverage temporary objects for use within the scope of your query session or batch. There are many reasons why you may decide to use temporary objects and we will explore them later in this article. In addition to meeting various data-tier requirements, temporary objects can also be beneficial from a performance and scalability perspective.

SQL Server provides a few temporary objects options:

• Temporary tables both local and global
• Temporary storage procedures (not commonly used)
• Table variables

Why use temporary objects?

Some reasons for using temporary objects:

• No requirement for persistent data storage for a result set.
• Intermediate state needed only temporarily, for example when performing data transformation or aggregation.
• Use of temporary storage only visible to the current session.
• Reduce query plan complexity.
• Portability of lookup-data by the containing modules, for example stored procedures.

The article will make heavy use of demos. I will be use demos to show syntax, overall capabilities, and usage scenarios. To write T-SQL code I opt for dbForge Studio for SQL Server. It is a powerful IDE for SQL Server management, administration, development, data reporting and analysis. The tool contains an excellent SQL code editor that provides SQL syntax check, snippets, automatic code formatting, and code completion.

Creating a temporary table

The following example demonstrates how to create a temporary table, insert some data from a permanent table into the temporary table, and select data from the temporary table.

The query returns the following result

Note the # pound sign indicates that we work with the temporary object. To remove the temporary table explicitly, we can execute the following query:

SELECT INTO a temporary table

You can use SELECT INTO statement to create a temporary table implicitly. The following example creates a temporary table and populates it with data from the permanent table.

If you need an empty temporary table based on the schema of an existing table, and you do not want to populate it with rows, you can do the following:

In this case, zero rows will be affected. If you try to select from the temporary table, no rows will be returned.

Temporary table scope and lifecycle

Let us get back to the SELECT INTO example discussed above. If you disconnect and connect back to an SQL Server instance, the temporary table will no longer exist. If you then try to query from the temporary table you will get an error message – Invalid object name ‘#temp_contact’.

So why explicitly drop the temporary table? You can think about tembdb usage for larger modules with longer session durations. You can also think about numerous concurrent module executions as well as about modules with many temporary objects.

Viewing temporary table metadata

Once we have created a temporary table, we can execute a system-stored procedure that reports information about a database object, a user-defined data type, or a data type.

However, in this case we get an error. We can go ahead and try this in tempdb.

We will see the table name and the value appended to the end. We can use this name to see an entry for this table in sys tables.

This query returns full information about our table.

Temporary table data types

In this example, we will create a temporary table with a list of supported data types. The following query executes successfully, which means all these data types are supported.

However, there is an exception. Let’s try to create a new temporary table with user-defined data type. The following query will return an exception.

If you need to use user-defined data-type, you need to use a temporary table. The following script executes correctly.

Then you can use the tempdb and the DROP TYPE statement to cleanup.

Temporary tables and dynamic SQL

You can use dynamic SQL to create a temporary table. The following example demonstrates how to do it.

However, if you try to execute the INSERT and then SELECT statements, you get an error.

The above-mentioned query returns the “Invalid object name ‘#category’”. To avoid this, you need to include INSERT and SELECT within the dynamic SQL, as it shown in the following example.

In this case, you are able to create the temporary table, insert rows, and select from that table.

Local and global temporary tables

As you already know, a single pound sign is used to create a local temporary table (e.g. #localtemptable). If you try to insert some data to a local temporary table from within another session, you will get an error message saying “Invalid object name”. In order to create a global temporary table, you need to add an additional pound sign to a table name (e.g. ##globaltemptable). In this case, you are allowed to access this global table from a different session.

Table variables

SQL Server provides table variables start with 2000 as an alternative to temporary tables. In some cases, a table variable can be more useful than a temporary table.

Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example.

So here, we created a local variable. Note we have no GO in between the declaration statement and the ISERT statement. We inserted some data from the permanent table and then selected data from the variable. There is no need to explicitly DROP the table variable.

Table variable scope and lifecycle

Let’s brake the previous example into several steps. First, declare a table variable and execute the query.

It executes successfully. Now if we try to insert data into the variable

We get an error message like “Must declare the table variable ‘@category’”. This is because the scope is for the duration of batch execution. So one of the main advantage is that there is no need to cleanup at batch completion, implies shorter lifecycle. The main cons is that it cannot cross batch boundaries.

Advantages and disadvantages of using temporary objects

Temporary tables advantages

• Provide intermediate result-sets that can reduce query plan complexity for large or complex workloads. It is not a guaranteed solution, but a valid tool for query plan quality issues.
• Ability to isolate a table’s data ‘per-user’ execution.
• Provide column-level statistics like permanent tables.
• You can use TRUNCATE, SELECT INTO, CREATE INDEX, ALTER TABLE, IDENTITY_INSERT, ROLLBACK TRANSACTION.

Temporary tables disadvantages

• Heavy tempdb usage via object creates/drops can lead to latch contention.
• Not always optimal compared to inline, single-statement alternative.
• Not supported in user-defined functions.
• Inherit tempdb collation for non-contained databases.
• System caching of temporary tables and associated statistics may cause unexpected query plan shapes and incorrect statistics.

Table variables advantages

• Can be used within scalar and multi-statement table-valued functions.
• Automatically inherit the current databases’ collation implicitly.
• Will not directly affect recompilations.
• Can be passed as input parameters to stored procedures.

Table variables advantages

• Heavy tempdb usage via object creates/drops can lead to latch contention.
• Non-column level statistics
• Cannot TRUNCATE, SELECT INTO, CREATE INDEX, ALTER TABLE, IDENTITY_INSERT, ROLLBACK TRANSACTION

Resume

One of the key factor influencing temporary objects usage is the performance and scalability. This means, for complex queries, sometimes breaking a query down into steps with temporary objects can improve query plan quality and increase performance.

Reference: Pinal Dave (https://blog.sqlauthority.com)