You must Sign In to post a response.
  • Category: SQL Server

    TempTable and Main Table

    Why we are having temp table in SQL SERVER? Instead of having Temp table we can insert the data directly into the main table right. why passing the values into Temp and inserting into main Table.
  • #761379
    Hello Geetha,

    There are two types of temp tables in SQL Server :

    Local temp : Local temp 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 is stared with hash ("#") sign.

    --> CREATE TABLE #LocalTempTable( UserID int)

    Global temp : Global temp tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

    --> CREATE TABLE ##GlobalTempTable( UserID int)

    Hope this will help you.

    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #761383
    Temp table for temporary purpose. After its purpose it will not be in the the database. So the size of the database will not increase
    By Nathan
    Direction is important than speed

  • #761387
    Hi Geetha.

    As we make use of temporary variables in programming just to store value for some scope, in database tables also we create temp tables to store few result set.

    Later on we can copy those result set from temp table to our main table, then we delete the temp table after making use of it.


    Thota Sridhar.
    If you learn from defeat..
    You haven't really lost..

    Sridhar Thota.
    Editor: DNS Forum.

  • #761416

    This is a very good question. Even every developer would get this question on Temporary table.

    The best thing to consider with temporary table is that the scope of the table will get off when the session is closed. This means that temporary tables will be automatically deleted by the database engine when the connection/Session gets closed.

    If you create Table and insert data temporarily, you have to take care of manually deleting the table when the task is completed. As Most of the database developers are missing to drop the tables and are not ready to take this additional overhead, We are using temporary table for this purpose.

    When we use temporary table, The size of the database remains the same and will not get increased. Even if it is increased to hold the temporary storage, It will get cleaned up and the spaces will be reclaimed,

    Please mark this as Answer, if this helps

    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...

  • #761418
    Hai Geetha,
    There are various reasons, we use the temp tables in the Sql Server developments:
    1. Temp tables are used for temporary purpose. So if we need some intermediate calculations of the data and finally want that data as per the internal calculations, we can use temp tables.
    2. Temp tables have the scope limited to the stored proc, so the memory which will be used in internal calculation will be wiped out once it is out of scope.
    3. Temp tables are based on the sessions, so for each user, there will be separate temp table and when for the particular session, it comes out of the stored proc, the memory will be deallocated. This thing will not be for the general tables.
    4. If we use temp tables, we are not increasing the size of the database.
    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • #761424
    Temp table is valid for a single connection only. it mostly used when we need more calculation of process to do mostly in loops. since it wont be saved in db, it wont take much space.
    Do Good... Enjoy your life.....

  • #761456

    If we want to perform action once, at that time we can go with temp table after complete that action automatically temp table is deleted. But this is not happen when we go with table, its occupy memory, if we want to delete the table we need to perform some action in to that.

    Hope you understood...

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

Sign In to post your comments