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 ]



     










     

    Comments

    Post a Comment

    Popular posts from this blog

    File operations in C# (.net)

    -
    Here we are performing some basic file operations which widely needed in applications. ASPX :     <div>         <asp:Button ID="Button1" runat="server" Text="CLick" onclick="Button1_Click" />         <asp:FileUpload ID="FileUpload1" runat="server" /><br /><br />          </div> C# :        using System.IO;        using System.Text;         protected void Button1_Click(object sender, EventArgs e)         {             string path = @"E:\MVC Application Demos\ABC.txt";            // File Path taken             string name = FileUpload1.FileName.ToString();                // Here we get File Name only             string pa...
    Read more

    Add Serial no in crystal report without coding

    -
    Image
    Adding a serial number in grouping without any code. I have placed the step by step screenshots which will explain easily how to do it.   1) Add a new field in Running Total Fields in Field Explorer   Give a proper name to your Running total name. For example I have choosen here RTotal0. Now categorize based on which field in database you want to summarize it. Next is as we want serial number select count. If you want your serial number to start again from the start when the value in the group changes then select on change of group and specify the grouping name else keep the default value.   Smaple Preview of the Design View of Report where I Have added SRNo column at the start in grouping section.  
    Read more

    How to get top " n " number of rows from table

    -
    Suppose you want top n values from table , so execute following query with replacement of following item as mention below : 1 )  Replace your our tablename with [table_name] 2 )  Replace your "n" with row numbers  (how many rows you want ) [ Note :  You can do multiply and division operations also ] Query for Top rows : SELECT MainSQL .* FROM ( SELECT t .*, ROW_NUMBER () OVER ( ORDER BY 1 ) Row_Number , COUNT ( 1 ) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count FROM (SELECT * FROM [table_name] )  t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1 AND Row_Number < ( 1 + 2 ) ORDER BY Row_Number ; Query for Bottom  rows : SELECT MainSQL .* FROM ( SELECT t .*, ROW_NUMBER () OVER ( ORDER BY 1 ) Row_Number , COUNT ( 1 ) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_C...
    Read more