Working with Temporary Objects in SQL Server

In this article, we will learn about Temporary objects like Temporary tables in SQL Server. We will also see the different scoping levels for Temporary objects.

So the first question that comes to mind is why should we learn about Temporary objects? There are various scenarios where you can take the advantage of temporary objects. Some of them are as follows –

• The data which you don’t want to persist in the database for the future usage can be stored in temporary objects.
• You may have some Computed data which you want to use in stored procedures during business processing logic.
• Sometimes you may want to make data visible to a particular session only. In that case, you can store the data into temporary objects.
• You don’t want to clean-up the data explicitly, so you can store the temporary data into temporary objects.

Now let’s start with a demonstration by creating temporary objects. You can create various temporary objects like –

• Temporary Tables.
• Temporary Stored Procedures.
• Temporary Variables.

The temporary objects can be created as Local objects (using # sign) or global objects (using ## sign). The local temporary objects are available at session level where as the global temporary objects are available across all the sessions.

For this demonstration, I am using SQL Server 2012 with the demo database Northwind. Let’s open SQL Server Management and start creating the temporary objects as described below –

· We will first of all create a temporary table and insert the data in the table from existing table. The script is as shown below –



Now save the query file with the name TempObjects.sql and close the query window. After this, open the query window and then try executing the select statement

SELECT * FROM #CustomerCopy

You will get an error as shown below – .



The error says that object does not exist. If you see the script, we have created a temporary table using (#) sign. The single ‘#’ denotes that you have created a Local Temporary table. The life time of the local temporary table is till the session is on. Since we closed the query window, we lost the session.

Now lets rerun the above script and try to access the temporary object in a new query window by using the same SELECT command we just used -



The same error occurs as we lost the current session when we opened a new query window.

Global Temporary Table in SQL Server

Now lets create the same demonstration using Global (##) temporary table and analyze the life time of the table. In the above script, while creating a temporary table, use ‘##’ sign and create the table. The rest of the script will remain same except the table name with ‘##’ sign during insertion and selection. The table script will look like below –



Run the Select statement into another query pad. You will see the following result –



Now let’s see some examples on Select * INTO. Start by writing the following queries –



The above query will create a temporary table which will copy all the rows from Customers table into our CustomerCopy table. If you need only schema of the table, you will have to apply a false condition at the end of the query. For example – WHERE 1=2. This condition is always set to false which will result into a copy of the table without any data.

Try viewing the temporary table metadata. For the non-temporary tables, we will make use of sp_help stored procedure. But for the temporary tables, we will have to make use of TempDB database and make use of sp_help stored procedure to view the metadata as below –

Temporary Table with User Data Types

Let’s use the User Data Type in our Temporary table. Write the following script –



When you try to execute the above script, you will get an error as described below –



The above error says it cannot find the type “CustomerAddress”. But we have already created the type. Well, if you want to use User Types into Temporary tables, you will have to create them into TempDB database. So, let’s create a User Type into our TempDB and create temporary table into our Northwind database as shown below –



The above query will get executed successfully and your temporary table will get created. You can use sp_help stored procedure to check the data types of the above created table.

Temporary Table using Dynamic Queries

Let’s now create temporary tables using dynamic query. There are couple of restrictions which you will have to consider while creating temporary tables using dynamic SQL as described below –

• When you create temporary tables using Dynamic SQL, you cannot just first create the table and perform insert/update/delete/ select statements after creation. You will have to create dynamic SQL to create a table, then insert rows in the table and then select them and update them as per our requirements. All this has to be done using dynamic SQL query only.
• The other option could be to first create a temporary table and then use dynamic SQL to insert the data. Then use simple select statements and again use the dynamic SQL for updating the data of temporary tables. This will work. Because the temporary table is available in the Dynamic SQL.

You will have to make a clever choice of how do you want to create temporary object while working with dynamic SQL. Let’s see some examples of the same.



Now try writing a Select or Update Statement after creating the dynamic SQL and you will receive the following error.



The error shown in the above result is for the select statement which I have written after the dynamic SQL.

Create Temporary Table using Constraints

Now let’s take a look at how to create temporary tables using constraints. For this demonstration, we will create two tables as shown below –



The above queries make uses of Primary key, Not Null and Check constraint. Now let’s try taking the reference of #Exception1 table into #Exception2 table using foreign key. The query and result of the query is as shown below –



The above query execution tells that the Foreign key constraint cannot be enforced on local or global temporary tables. When you are creating temporary tables, you may want to create the indexes on the table for faster data retrieval operations. You can create an index for example Clustered Index, Non Clustered Index or Unique index on temporary tables. For example –

 

The above index query will create a non-clustered index on Name column of our #Exception1 table. Likewise, while creating a temporary table, you can apply identity to the column to auto generate the values, as well as you can also perform transactions with the temporary tables.

Conclusion

In this article, we have seen how to create temporary objects like temporary tables which we can use to store data which we don’t want to persist in the database.

In the next article, we will touch base on Temporary Table Variables and also see some limitations while working with table variables compared to temporary tables.