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

    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

    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 path1 = FileUpload1.PostedFile.FileName.ToString();    // Here we get File Path only             if (File.Exists(path.ToString()))             {                      //     ClientScript.RegisterStartupScript(this.GetType(), "myalert"
    Read more

    Working with Oracle's BLOB and Microsoft's C#

    -
    We used this title for describing technologies , we are working in. Here we will see "  How to store files in oracle database using c# ?   " Step 1:           Use file uploader controller to browse directories. Step 2:          Before uploading file or its path , we need to understand BLOB of Oracle          BLOB  ( Binary Large Objects ) :                                                    File content store in binary format.In Oracle , BLOB and    CLOB  (Character Large Object) used.Technically BLOB prefered.For more info regarding BLOB click here  . Step 3 :        In C# , use System.IO namespace for file operations.     Step 4:         string path = FileUpload1.PostedFile.FileName.ToString();         string FileName = FileUpload1.FileName.ToString();         string FileSize = FileUpload1.PostedFile.ContentLength.ToString();         string FileContent = FileUpload1.PostedFile.ContentType.ToString();         string filename = Path.GetFileName(path
    Read more