Local Temporary Tables and Table Variables

  • peterhe

    SSChampion

    Points: 11363

    Comments posted to this topic are about the item Local Temporary Tables and Table Variables

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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)

    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
  • humbleDBA

    Hall of Fame

    Points: 3440

    Agree with Gail, nice article, plus I'm reading it that you're suggesting that Data Storage for Temp Table is not in memory - for which it is in-memory unless pushed out to disk.

    Also, I'm taking it that, as this article is not an old one, that it covers SQL2005. If this is the case, then INSERT...EXEC is supported for Table Variables. You can test this with the following:

    -- SQL2005

    DECLARE @helpdb TABLE (name sysname, db_size nvarchar(13), owner sysname, dbid smallint,

    created nvarchar(11), status nvarchar(600), compatibility_level tinyint)

    INSERT @helpdb (name, db_size, owner, dbid, created, status, compatibility_level)

    EXEC dbo.sp_helpdb

    SELECT * FROM @helpdb

    HTH

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Awesome article that covers some of the lesser considered aspects of temporary tables and table variables. Thank you.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • David Quéméré

    SSC Rookie

    Points: 27

    The 2nd chapter about UDTs contains a mistake : the script supposed to raise an error and the workaround don't have any difference. I guess the error script should use a UDT (dbo.Name) instead of a native type (nvarchar(50)).

    Thanks to all the authors and SqlserverCentral for their efforts and the useful documentation they bring us.

  • Jeff Moden

    SSC Guru

    Points: 995099

    Good article!

    You did forget to mention one workaround for the UDT's that would allow it to work across all databases including TempDB... Define the UDT in the Master DB. Of course, most folks will argue that the Master DB is a sacred entity that should not be touched and, for the most part, I agree. 😀

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • RBarryYoung

    SSC Guru

    Points: 143327

    Of course we use a startup procedure to define the UDT's in TempDB.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Anipaul

    SSC-Insane

    Points: 24681

    Wonderful article..

  • Gail Shaw

    SSC Guru

    Points: 1004446

    rbarryyoung (9/1/2008)


    Of course we use a startup procedure to define the UDT's in TempDB.

    Does defining them in model work as well?

    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
  • Christian Buettner-167247

    SSChampion

    Points: 13729

    GilaMonster (9/1/2008)


    rbarryyoung (9/1/2008)


    Of course we use a startup procedure to define the UDT's in TempDB.

    Does defining them in model work as well?

    According to this article, yes

    http://www.sqlservercentral.com/articles/Advanced/3104/

    Best Regards,

    Chris Büttner

  • peterhe

    SSChampion

    Points: 11363

    To humbleDBA,

    You are right. INSERT ... EXEC works for table variable too in SQL Server 2005. In SQL 2000, it does not work. Thanks.

    Jeff, Gail, and Chris,

    Thanks for the workarounds for UDTs in master and msdb. I have not had a chance to test and confirm it.

    David Quéméré,

    Your are right. The script has a mistake. It's actually the workaround script. I guess I messed it up by copy/paste

    Thank you guys for taking time to read/comment on it

  • paul.t.silvey

    SSC Enthusiast

    Points: 175

    hmmm... I personally hate table variables because they cause the disk queue length (I/O) and CPU to shoot through the roof. They only work on simple small systems with low transactional volume. If you take a real life OLTP system or a large warehouse, they are pretty much useless in my book. They seem to bring the system to its knees. They kind of remind me of Cursors... wait... aren't cursors memory intensive also? Do you see the similarities?

    I will take an indexed temp table over a table variable any day on a heavy usage system.

    SQL Silvey

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Probably because of the affect they have on queries. Bad cardinality = bad query plans = higher than expected IO and CPU.

    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
  • Jerry Hung

    SSChampion

    Points: 12908

    I have a question, hopefully you guys will know better than me

    We use a lot of TABLE VARIABLES (not temp tables) in our select statements for our heavy traffic websites

    Server has 4GB RAM. Windows 2003 Standard x64, SQL 2008 RC0 64-bit

    SQL Server has been limited to max of 3GB memory, with 8GB page file

    Now the issue is our page file usage in Task Manager kept growing

    In 1~2 hours it has grown from 4.2GB to 9.5GB and not letting go so it seems

    There is no LONG running queries (no long duration, no high CPU)

    Probably a matter of time before it may reach the 12GB commited charge (4 physical + 8 page file)

    But does table variable get dropped??

    Noticed the article says "Current Batch" scope, while temp table is "Current Session"

    what is defined a batch? what if the front end .Net keeps re-using the same connection, therefore not dropping the table variable?

    Appreciate any help

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005
  • Gail Shaw

    SSC Guru

    Points: 1004446

    Table variables has the same scope as normal variables. Much less than a temp table. Their lifetime is limited to one set of commands (batch) sent to the server.

    So, if you send a bunch of ad-hoc SQL to the server (using Execute or ExecuteNoQuery or the like) and that ad-hoc SQL declares and uses a table variable, the table variable only exists for the duration of that piece of ad-hoc SQL.

    Are you running SQL Enterprise or Standard?

    Have you checked to see if the page file usage is coming from SQL or from something else on the box?

    Lastly (not a perf issue) RC0 isn't licensed for production usage (unless you're a member of the early-adopter program) and has a built-in expiry date. You should replace that with SQL 2008 RTM as soon as you can.

    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

Viewing 15 posts - 1 through 15 (of 35 total)

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