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.

SQL SERVER - Story of Temporary Objects create_table_1

The query returns the following result

SQL SERVER - Story of Temporary Objects temp_table_query_results_2

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

SQL SERVER - Story of Temporary Objects removing_temporrary_table_3

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.

SQL SERVER - Story of Temporary Objects select_into_temp_table_4

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:

SQL SERVER - Story of Temporary Objects empty_temporrary_table_5

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

SQL SERVER - Story of Temporary Objects empty_table_6

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.

SQL SERVER - Story of Temporary Objects temp_table_metadata_7

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

SQL SERVER - Story of Temporary Objects temp_db_temp_table_metadata_8

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.

SQL SERVER - Story of Temporary Objects sys-table-query_9

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.

SQL SERVER - Story of Temporary Objects example-data-types_10

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.

SQL SERVER - Story of Temporary Objects user-data-type_11

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

SQL SERVER - Story of Temporary Objects temp-db-for-user-data-type_12

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.

SQL SERVER - Story of Temporary Objects temp-table-via-dynamic-SQL-13

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

SQL SERVER - Story of Temporary Objects insertinto-temp-table-via-dynamic-SQL-14

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.

SQL SERVER - Story of Temporary Objects include-insert-select-in-dynamic-SQL-15

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.

SQL SERVER - Story of Temporary Objects table-variable_16

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.

SQL SERVER - Story of Temporary Objects declare-table-var_17

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

SQL SERVER - Story of Temporary Objects insert-into-table-var_18

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)

Quest

Devart, SQL Scripts, SQL Server, SQL TempDB
Previous Post
SQL SERVER – List the Name of the Months Between Date Ranges – Correction
Next Post
SQL SERVER – Puzzle – Why Decimal is Rounded Up?

Related Posts

5 Comments. Leave new

  • Hi,
    Awesome !!
    One error above.Make it Table variables disadvantages instead of Table variables advantages last header before Resume.

    Also table variable cannot be use in dynamic sql.

    Thanks

    Reply
  • As mentioned in ‘Temporary tables and dynamic SQL’ section of this post,
    we can not access #temptable(created in dynamic sql) at outside the dynamic sql scope

    But we can access #temptable(created outside the dynamic sql scope) in dynamic sql.

    BEGIN
    if OBJECT_ID(‘testdb..#TestTable’) is not null
    drop table #TestTable

    CREATE table #TestTable(id int, name nvarchar(10))

    EXEC (‘insert into #TestTable values(1,”sanjay”);
    –>–Inside the dynamic sql
    select * from #TestTable’)

    –>–Outside the dynamic sql
    SELECT * FROM #TestTable

    drop table #TestTable
    end

    Reply
  • As mentioned in ‘Temporary tables and dynamic SQL’ section of this post,
    we can not access #temptable(created in dynamic sql) at outside the dynamic sql scope

    but we can access #temptable(created outside the dynamic sql scope) at dynamic sql.

    BEGIN
    if OBJECT_ID(‘testdb..#TestTable’) is not null
    drop table #TestTable

    CREATE table #TestTable(id int, name nvarchar(10))
    EXEC (‘insert into #TestTable values(1,”sanjay”);
    –>–Inside the dynamic sql
    select * from #TestTable’)

    –>–Outside the dynamic sql
    SELECT * FROM #TestTable

    drop table #TestTable
    end

    Reply
  • Thanks Pinal.

    I was having one of the use cases where dynamic temp objects were creating lot of overhead, I used them for simplifying my query and to avoid subqueries. This was horrific, I tried experimenting with sql plans and got to know most of the time consumption was during temporary objects creation and data manipulation inside them, I removed them and performance got decent enough. Thanks again for sharing this nice article, helps in understanding temp objects in good way.

    Reply
  • how to create a cache table intermediate with stored procedure

    Reply

Leave a Reply