Post reply

table variable and tempdb

  • June 18, 2009 at 9:33 am

    #135368

    when I create a table variable will it create in tempdb or local db where I execute

    Rajesh Kasturi

  • June 18, 2009 at 9:43 am

    #1011869

    i beleieve Tempdb this article may be of use to you:

    http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

    Gethyn Elliswww.gethynellis.com

  • June 18, 2009 at 9:51 am

    #1011881

    Both temp tables and table variables are created in TempDB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 18, 2009 at 10:33 am

    #1011926

    Temp tables are created in TempDB Database and require more IO resources and locking. It maintains statistics and allows explicit indexing. We can use ‘into clause’ to create it. Table variable is a memory structure. If it holds more data than it can fit in memory, it is created in tempdb. Table variable does not maintain statistics. So, explicit indexing is not allowed. It requires less locking and logging resources because table variables have limited scope and are not part of the database, transaction rollbacks do not affect them. Derived tables are created in memory as table variable.

  • June 18, 2009 at 10:45 am

    #1011933

    John Doe (6/18/2009)

    Temp tables are created in TempDB Database and require more IO resources and locking. Table variable is a memory structure. If it holds more data than it can fit in memory, it is created in tempdb.

    Both temp tables and table variables are created in TempDB when they are created/declared. They are both kept in memory as much as possible and they have the same rule for spilling to disk if there's memory pressure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 18, 2009 at 11:35 am

    #1011955

    Also keep in mind performance of Table Variables versus Temp Tables. Use the appropriate one for your need and size of data that will be used. Test first before implementing either one in production.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 22, 2009 at 11:36 am

    #1013481

    Hi All,

    Thanks for the help...

    Rajesh Kasturi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply