SQL Server Temporary Tables

Hello Folks,

You would have heard about this table earlier. I have make some keynotes on this, might make you feel easy about this.

Microsoft SQL Server provides the concept of temporary table which helps the developer in a big way.  Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. These tables can be created at runtime and can do the all kinds of operations that one normal table can perform. It acts as a vehicle for passing data between objects.

According to the table-types, the scope is somewhat limited. These tables are being created inside tempdb database.

Well based on their behavior and scope of the table, SQL Server provides two types of tables:

• Local Temporary Tables
• Global Temporary Tables

I will discuss each one of them one by one:

Local Temporary Tables:

• Local temporary tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances.
• Local temporary table name starts with hash (“#”) sign.
• This will become more clear to you, if you see this example:

First, we create a local temporary table named – “#Student”.

CREATE TABLE #Student(
S_ID INT PRIMARY KEY,
StudName VARCHAR(50), 
StudBranch VARCHAR(50))

Second, we can also insert values as we do for the normal table.

INSERT INTO #Student VALUES ( 1, 'Piyush','Computer-Science');

Now, if want to check the value without closing the query window and working on the same, then;

SELECT * FROM #Student

If, we somehow close the current query window and again execute the SELECT or INSERT command, then it will generate an error:

If you want to examine the name of your temporary table which is stored in sys.objects, then just write the following code:

SELECT name FROM tempdb.sys.objects
WHERE name LIKE '#Stu%';

The result can be seen as:

Most of the objects can have names upto 128 characters, but the temporary tables can have a name length of 116 characters, and the last 12 characters can make the name Unique.

Global Temporary Tables:

• A global temporary table has been created by a connection just like a permanent table, and then it is made available to any user by any connection.
• It can only be deleted once all connections have been closed.
• We can also define in the ways that the life of the table extends until the last session accessing the table disconnects.
• Global Temporary tables name starts with a double hash (“##”).
• This will become very clear if you follow this example:

First, we create a global temporary table named – “##Global_Student”.

CREATE TABLE ##Global_Student(
S_ID INT PRIMARY KEY,
StudName VARCHAR(50), 
StudBranch VARCHAR(50))

Second, we can also insert values as we do for the normal table.

INSERT INTO ##Global_Student VALUES ( 1, 'Piyush','Computer-Science');

Now, if you want to check the value, then it is visible to all the SQL Server connections.

SELECT * FROM ##Global_Student

Every time the SQL Server is restarted, it dumps and rebuilds tempdb.

Well, this was all about Temporary Tables in SQL Server. In the next blog post, I would like to deal with Table Variables.

So be tuned 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook