With this blog post, a continuation of earlier post on “TEMPDB – the most important system database in SQL Server“, we are going to see more about the temporary objects created in TEMPDB database. There are basically 3 different types of temporary objects that can create manually as below.

1. Local Temporary Tables

Local Temporary tables are created for operations that may require to access a specific set of data instead of querying every time the actual table. This will reduce the number of reads on the actual table and reduce the blocks/locks on the table.Everytime, you create a temp table (irrespective of the databases), the temp table is created in TEMPDB database.

Scope of the Local Temporary tables

The scope of the temp table is session/connection based, hence if you create two connections (two different management windows) and create the temp table with the same name, it will create two different objects in TEMPDB.

open two Windows and execute the below
create Table #T1(Col1 int)
--Get the temp table created using the below
SELECT
    *
FROM
    sys.tables AS T
WHERE
    T.name LIKE '#t%' ;

However, as local temporary tables scope is session/connection based, these objects can still be used at nested levels.

But, wait, Will you be able to use temporary table in a function? – BIG NO. That is one of the difference between procedures and functions.

It is important to understand about temporary table caching in sql server

2. Global Temporary Tables

Global Temporary tables are again another temporary tables in SQL Server created in TEMPDB. Those are created by prefixing “##” the tablenames.

Scope of Global Temporary tables

Global Temporary tables are visible to all sessions once its created. On the flip side, there will be more contention if global temporary tables are heavily used. In addition, the scope of the global temporary table ends immediately when the session that created is terminated (or) any other session that uses the object is dropped manually. So, if you are planning to use global temporary tables, you should be very careful on its usage.

3. Temporary procedures

I believe, most of us are familiar with Temporary tables. But what about temporary stored procedures? Yes, just like temporary table, you can create temporary procedures as well in two ways – Local and Global. As we discussed earlier, the scope of the Local and Global procedures exactly same as temporary tables. These temporary tables are created in TEMPDB.

Let us look at the usages of temporary procedures. I could not find any good reason for using temporary procedures, may be, we have not used with this. But, it may be helpful while testing any procedure where we do not want to pollute the database with unnecessary objects. While deploying database, we may need to create temporary procedures for any purpose which we may not be using later etc.

If you have any use case, I would request to share your ideas.