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

1_SQL_Server_Temporary_Tables

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

2_SQL_Server_Temporary_Tables

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:

3_SQL_Server_Temporary_Tables

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

4_SQL_Server_Temporary_Tables

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

One Comment on “SQL Server Temporary Tables”

  1. thank you very much for post.

    i wnat to know differnecr between @temp table and #temp table

    ex :

    CREATE TABLE #myTemp (id int IDENTITY(1,1) )
    and
    DECLATE @mytemp TABLE(id int IDENTITY(1,1) )

    as i worked with both of these, the functionaliy of both are same.
    then what is difference between them??
    Regards
    Rikin

Leave a Reply

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