Friday, 4 October 2013

Temporary tables in SQL Server

Hi friends , Yesterday I thought about temp. tables in database . After searching over same query , I got through some links regarding
"Temp Table " ,
"global temporary table ",
"Local Temp Table",
"#"  etc.
  • Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an   additional overhead and can causes performance issues.
  • Number of rows and columns need to be as minimum as needed.
  • Tables need to be deleted when they are done with their work. 

  • Till the date I am using simple method to do so . But this concept will save my time and don't confuse between oracle and sql server. Its different.

    Concept : Temporary tables in Database

    Concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside system database/tempdb/temporary tables (in SQL Server) database.

    Different Types of Temporary Tables

    SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
    • Local Temp Table
    • Global Temp Table

        1) Local Temp Table

               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.

        2) Global Temp Table

              Global Temporary 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.

    If you want see all opearation under one page , simple go for procedure. So your concept will gets clear.

    Steps To use it

    Step 1:

    (As shown in picture)
    Create procedure with parameters and write code .



    Step 2:

    First check your temp table created or not .
    Refresh the database. In System database - > tempdb ->  temporary table
    if done , go for executing stored procedure.



    Step 3:
    Please check result.





    Drop temp table
      
     drop table #LocalTempTable
    or
    Prefer Link
     
     
    It will be helpful when ,
     
    - we are doing large number of row manipulation in stored procedures.
    - This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
    - we are having a complex join operation.


    For Oracle ,

    Some Important links need to refer.
    1) temp Table in Oracle
    2) Problems with temp table in Oracle


    Quote :
    Be a gentle friend to trees and they will give you back beauty, cool and fragrant shade, and many birds, singing ]



     










     

    No comments:

    Post a Comment