Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Temp Table and Temp variable in sql

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 297
    Comment on it

    Temp Table -Temporary Temp Tables

    1)They can be created at run time in Tempdb database.

    2)DDL,DML statements like select,update which can be executed on regular tables can be done on temporary tables. They can have constraints and can be indexed.

    3)Stored procedure make use of Temporary Temp Tables. In Stored procedures data is manipulated via complex query. In case of complex queries, temporary table is used to store the output of one query / intermediate results, which then can be used for further manipulation via joins etc to achieve the final result.

    Two types of temporary tables:

    1)Local Temp Table

    Syntax to define Local Temp Table

    CREATE TABLE #LocalTemp
    (
     column-list
    )
    

    The Local temp table visibility is limited to the current query window or current session that created it. An error is raised if user try to use temporary table in other query window other then the query window that created it. The temporary table is automatically deleted if current query window that created temporary table is closed.

    2)Global Temp Table

    Syntax to define Global Temp Table

    CREATE TABLE ##GlobalTemp
    (
     column-list
    )
    

    They are visible to all sql query window after they are created, and deleted when sql server sessions or query window that created it have been closed.

    Table variable

    Syntax for Table variable:

    DECLARE @TableVariable TABLE 
    (
     column-list;
    
    )
    

    Table variable is a local variable. It's scope is limited to function,procedures and batches of query execution in which it is defined. Table variable is not stored in the memory but is automatically stored in Tempdb database. Table variable allows to create primary key, identity at the time of Table variable declaration but does not allow to create non-clustered index. DDL,DML statements cannot be applied to Table variable.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: