Local Temporary Tables and Table Variables

  • Yes, I did.

    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
  • Thanks. That is what I thought.

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

  • One more major diff.

    You can not use temp tables in UDF

    But can use table variables in UDF....

  • True enough. You should also mention that most UDFs aren't necessary either. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • GilaMonster (8/31/2008)

    Very nice.

    Just one small thing. You say that for data storage, temp tables are in TempDB and table variables are in memory and tempDB. I may be misunderstanding what you mean there, but temp tables are also memory resident unless it becomes necessary to write them to disk (memory pressure, too large a table)

    Also, the Appendix states that table variables are held in memory and temporary tables are held in tempdb. Whilst usually correct*, it is also the case that both are physically created in tempdb, as can be demonstrated by running the following code on a SQL Server which has no other activity occurring (runs on SQL 2000, 2005 and 2008):

    -- make a list of all of the user tables currently active in the

    -- TempDB database

    if object_id('tempdb..#tempTables') isnot null droptable #tempTables

    select name into #tempTables from tempdb..sysobjectswhere type ='U'

    -- prove that even this new temporary table is in the list.

    -- Note the suffix at the end of it to uniquely identify the table across sessions.

    select * from #tempTables where name like '#tempTables%'


    -- create a table variable

    declare@MyTableVariable table (RowID int)

    -- show all of the new user tables in the TempDB database.

    select name from tempdb..sysobjects

    where type ='U' and name notin (select name from #tempTables)


    * According to KB305977, a table variable can hold more data than could fit into memory.


Viewing 5 posts - 31 through 34 (of 34 total)

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