T-SQL Ninja #27

Temporary Objects

What are temporary objects?

Basically SQL Server allows you to create temporary objects that you can work with. This is convenient because you don’t necessarily want to persist every intermediate result or help table in your database when working with large amounts of data. Temporary means that the tables are stored in the tempdb and are automatically deleted. When they are deleted varies, so we will take a closer look at that in a moment. But there is not only the possibility to create tables temporarily, this also works with procedures.

How can you work with temporary objects?

There are basically two types of temporary objects in SQL Server: local and global temporary objects. You can recognize temporary objects by the fact that their names begin with a hash (#). You can tell whether an object is local or global by whether it has one or two hash marks in front of it. Let’s look at the differences between the two objects. First, we will create a local temporary table:

DROP TABLE IF EXISTS #temporary
CREATE TABLE #temporary
(
     ID int IDENTITY(1,1)
    ,Ninja varchar(150)
)

Now we can insert into this table and query it.

INSERT INTO #temporary (Ninja) 
VALUES ('Lushikute'), ('Chimozuki')

SELECT 
     ID
    ,Ninja 
FROM #temporary

We can even index the table:

CREATE CLUSTERED INDEX ix_ninjaids
ON #temporary (ID)

To understand the difference between local and global temporary objects, you now open a new query on the database in Azure Data Studio or SQL Server Management Studio. If you now try to query the temporary table in the resulting new query tab, you will get the message that it does not exist.

So let’s do the same with a global temporary table:

DROP TABLE IF EXISTS ##temporary
CREATE TABLE ##temporary
(
     ID int IDENTITY(1,1)
    ,Ninja varchar(150)
)

INSERT INTO ##temporary (Ninja) 
VALUES ('Lushikute'), ('Chimozuki')

SELECT 
     ID
    ,Ninja 
FROM ##temporary

Not surprisingly, we get the same result up to this point as when we used a local temporary table. If you now query the global temporary table in the second tab, you will get results:

SELECT 
     ID
    ,Ninja 
FROM ##temporary

You have seen that you can only access a local temporary object within the session in which you created it, but you can access a global temporary object in other sessions as well.

And how long do the temporary objects “live”? That depends. A local temporary object ends with your session. This means that if the connection in your program code, ETL package, SSMS or Data Studio is closed in which the temporary object was created, then this object is no longer available. It’s different with global objects: as long as a session exists that uses the object, it is kept alive. If the last session in which the object was used is terminated, the object is also deleted and is irretrievably lost.

Now we have been working with temporary tables all this time, but speaking of objects, let’s create a temporary procedure for this week at the end:

DROP PROCEDURE IF EXISTS #sp_temp
GO

CREATE PROCEDURE #sp_temp AS
    SET NOCOUNT ON;  
    SELECT DB_NAME() AS ThisDB; 
GO

Again, you can use the procedure within the same session just like any other procedure:

EXEC #sp_temp

From another session this call fails again, because the object is not known to the SQL Server:

Could not find stored procedure '#sp_temp'.

If you create the procedure as a global temporary object, it will also be found in the second session:

DROP PROCEDURE IF EXISTS ##sp_temp
GO

CREATE PROCEDURE ##sp_temp AS
    SET NOCOUNT ON;  
    SELECT DB_NAME() AS ThisDB; 
GO

EXEC ##sp_temp

You can now also execute these in a second session:

EXEC ##sp_temp

Temporary objects can be useful for storing intermediate results in larger processes or for procedures that you do not want to use outside of a process. Of course, it is important to consider the scope of the object and to distinguish whether the object is also available outside the session.

References

Ninja-Notebooks @ GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *