Stairway to Advanced T-SQL Level 5: Storing Data in Temporary Tables

  • Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 5: Storing Data in Temporary Tables

    Gregory A. Larsen, MVP

  • What is the difference and implications of utilizing #TempTableName and @TempTableName ?

  • dataman777 (3/19/2014)


    What is the difference and implications of utilizing #TempTableName and @TempTableName ?

    See the following:

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

    --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)

  • Good examples in the article, Greg. I have to take exception to what you listed as "Best Practices", though...

    From the article:


    Here are few best practices to consider when creating temporary tables:

    Before you create a temporary table, verify you can't create a set based query that does not require the creation of a temporary table.

    Explicitly drop temporary tables as soon as they are no longer needed.

    The presence or absence of Temp Tables has absolutely nothing to do with whether something is set based or not. Further, it's frequently much more efficient to use a Temp Table in a "Divide'n'Conquer" fashion to hold interim results than it is to write a monster "all-in-one" query even if the creation of a midstream Temp Table causes a recompile. The use of Temp Tables can also give quite a performance boost if you have (for example) a CTE that is called more than once in the same query (the CTE will be executed once for each time it's called). Also, not everything that looks set-based, is. A recursive CTE that counts is much less effective than building a Temp Table to do the counting for you (for example, again).

    As to explicitly dropping Temp Tables as soon as they are no longer needed goes, "It Depends". Dropping Temp Tables in a batch oriented stored procedure will probably be fine although it could cause an unwanted recompile. Of course, that recompile could be very short and sweet compared to continuing using the memory that a Temp Table will take (and, yes, they start in memory and spill to disk only if they get too big). For procs that have a very high hit rate, it's usually better to NOT explicitly drop Temp Tables because, as of 2005, the skeleton of the Temp Table can be cached an reused making high hit rate code a bit faster and more efficient.

    --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)

  • Thanks, Gregory !!

    I heard that these days ( after 2008R2) we don't need to CREATE #Temp , you can just do SELECT * INTO #TEMP FROM dbo.Table, it won't be any difference in performance, locking, multitasking etc...

    Not sure if I want to put any indexes on #temp how this will work, can I do SELECT INTO #TEMP and then Create IDX?

    Appreciate your feedback..

    Tx

    mario

  • mario17 (4/3/2014)


    Thanks, Gregory !!

    I heard that these days ( after 2008R2) we don't need to CREATE #Temp , you can just do SELECT * INTO #TEMP FROM dbo.Table, it won't be any difference in performance, locking, multitasking etc...

    Not sure if I want to put any indexes on #temp how this will work, can I do SELECT INTO #TEMP and then Create IDX?

    Appreciate your feedback..

    Tx

    mario

    The ability to create and populate a Temp Table by using SELECT/INTO has existed since at least the RTM of SQL Server 6.5. And there will, many times, be a performance difference depending on what the end goal is. Lot's of times, SELECT/INTO will be a fair bit faster if no indexes are needed. "It Depends". 😉

    --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)

  • As to explicitly dropping Temp Tables as soon as they are no longer needed goes, "It Depends". Dropping Temp Tables in a batch oriented stored procedure will probably be fine although it could cause an unwanted recompile. Of course, that recompile could be very short and sweet compared to continuing using the memory that a Temp Table will take (and, yes, they start in memory and spill to disk only if they get too big). For procs that have a very high hit rate, it's usually better to NOT explicitly drop Temp Tables because, as of 2005, the skeleton of the Temp Table can be cached an reused making high hit rate code a bit faster and more efficient.

    To me the assertion regarding dropping vs. not dropping temp table seems to be contrary to what Paul White posted here:

    http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

    Apparently, DROP TABLE is not DDL

    Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS. None of these things prevent temporary table caching (so it does not matter whether you explicitly drop a temporary table at the end of a procedure or not).

    In what cases are you seeing different behavior? Trying to absorb as much as possible 😀

    Just a side note....This is also a good read

    http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

    </side note>

    Thanks

    Cheers

  • Greg

    Good, comprehensive article - thanks. Just one thing I thought I ought to pick up on:

    When a session creates temporary table that contains a named constraint inside a transaction, another sessions cannot create a temporary table of the same name until the first session commits the transaction

    That's not quite true. I posted in the Community Additions section of the page you linked to, because even the documentation appears to be wrong.

    John

    Edit - corrected typo

  • I'm curious as to how much you use #temp tables? Almost all of our SQL is for batch processing at night, written in the 'divide and conquer' format Jeff mentioned. Years ago when we first started writing SQL we created tables in a database called 'db_work'. You had an 'If exists..Drop table..' at the beginning of your SQL and another at the end to clean up these work tables, temp tables really since they were deleted at the end of the session. As more and more SQL was written we started running into issues where the same 'work' table name was being used in two different SQL's. So one would create the table and be running then the other started and the first thing it did was drop that table name, causing the first SQL to fail because the table didn't exists or wrong fields. So after some time of patching these types of issues we started to use the #temp tables more and more since we wouldn't run into this issue with the temp tables, we usually didn't code delete's at the end since they would automatically be dropped when the session ended. Now we have a lot of #temp tables being built and we are getting push back that the tempdb space is getting to large. We are now being told to not write any SQL with #temp tables, as this is not a 'Best Practice'. They are asking that we start using the 'db_work' database more for these type of tables. I'm worried that we will start running into the same issue we had before, where a two SQL's will use the same name and run at the same time causing one to fail. Plus I see that the issue of space will only switch from the tempdb to the db_work database.

    How do you find the balance of temp vs work table usage? If you use more work type tables, do you have a naming standard to avoid the issue I mention above?

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • Keep in mind that a local temp table can disappear on you if you mix queries that require separate connections. A new connection will be created without you knowing it and the temp table may seem to vanish. For example, if you create a local temp table, then run a firehose cursor and use the same connection for another query before fetching all the results, you will end up in a different session that doesn't have the local temp table.

  • Good refresher, thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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