Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
Drop temp table

Answered Drop temp table

  • Wednesday, February 20, 2013 7:32 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    I'm using SQL Server 2008. I had an argument with my co-worker over the correct way to drop a temporary table. In my stored procedure, I would normally have a CREATE TABLE #tt1 at the beginning, then have an insert statement into that temp table, and lastly, drop the temp table using simply DROP TABLE #tt1. My co-worker argues that, that was not a correct way of doing it. She said that with just that drop statement, that would not guarantee that the temp table will be dropped. She suggested to use this instead:

    IF OBJECT_ID('tempdb..#tt1') IS NOT NULL
   DROP TABLE #tt1

    I understand the statement, where it's first checking whether the temp table exist before you want to drop them. My question is, isn't a simple DROP TABLE #tt1 sufficient, do you need to do what she suggest? Isn't all temp table will be stored in tempdb anyway.

    Thanks in advance for your input.


    • Edited by i_h Wednesday, February 20, 2013 7:33 PM
    •  
     

All Replies

  • Wednesday, February 20, 2013 7:42 PM
     
     Proposed
    Sign In to Vote
    0
    There is no need to check for the existence if your procedure logic is such that the table is always created.  The argument that the drop statement alone will not guarantee that the table is drop is false.  If the table does not exist when the drop statement is executed, an error will occur. 
    • Proposed As Answer by Rogge Thursday, February 21, 2013 7:01 PM
    •  
     
  • Wednesday, February 20, 2013 7:49 PM
     
     Proposed
    Sign In to Vote
    0
    It'll get dropped either way.  The problem with not checking for existance first as she suggests is that you'll get an error if the table does not exist and you try to drop it.

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Proposed As Answer by sambeet Wednesday, February 20, 2013 9:13 PM
    •  
     
  • Wednesday, February 20, 2013 7:54 PM
     
     
    Sign In to Vote
    0

    Hi i_h

    I personally use the method suggested by you friend. However, I believe as mentioned by scott_morris-ga that if you do everything in the right order, any way is fine. I have seen much code in the manner you described.

    Pérez

     
  • Thursday, February 21, 2013 5:01 PM
     
     
    Sign In to Vote
    1

    Thanks guys for your reply. A follow up question. Same thing with this drop table. I used this sproc for my SSRS reports. Lets say user run this report that calling this sproc, that use temp table. Lets say the sproc ran on average for 2 minutes, or longer. Sometimes, the user don't have a patience, and X (close) the report in the middle of the report execution. In that case, what happen with the temp table that already been created? I noticed that the job is still running in the background, through Activity Monitor of SSMS. Does the query still running at the background?

    Also, lets say the same user rerun the report again right after that, i think SQL Server is smart enough to know which temp table that will be created, I believed padded with SSID or user id or machine id or whatnot. So, if you use the one suggested by my friend, will it drop the temp table from the previous run that was stopped in the middle? She argues that it will, but I still beg to differ. Thanks again for your insight.

     
  • Thursday, February 21, 2013 5:16 PM
    Moderator
     
     Answered
    Sign In to Vote
    2

    You will find these two posts from  Paul White are very informative:

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

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

     
  • Thursday, February 21, 2013 7:21 PM
     
     
    Sign In to Vote
    0
    thank you jingyang. Those links are very useful.
     
  • Thursday, February 21, 2013 8:20 PM
    Moderator
     
     
    Sign In to Vote
    0

    Don't drop the temp table. There's optimization to keep one page meaning less pressure on the SGAM page, but that assumes that you don't explicitly drop the temp table.

    Tibor Karaszi, SQL Server MVP | web | blog

     
  • Thursday, February 21, 2013 8:30 PM
     
     
    Sign In to Vote
    0

    I understand where you are coming from. My advice would be slightly different.

    Because I think it is a good practice to release unnecessary resources as soon as possible. I would say that it is a good practice to always explicitly drop the table that you created. Statements that follow might create another temp table with the same name, and not dropping the unused temp table in the same scope would lead to problems. Also, if there is a lot of data in the table, then dropping it releases resources.

    However, if you are confident that your temp table will mostly (or only) be created by your stored procedure, and it is always created with the same definition, then it is best to comment out the drop statement, and put a comment next to it to indicate you commented it out because of the special optimization that was recently introduced.

    By the way: do you know when this optimization was introduced? Was it with SQL Server 2008?

    Gert-Jan

     
  • Thursday, February 21, 2013 9:44 PM
     
      Has Code
    Sign In to Vote
    0

    If it is best to drop or not to drop the temp table depends on the circumstances (to drop or not to drop, that is the question).

    If the stored procedure is called in a loop, creating and dropping the table will be executed every time the loop is executed. Mind you, I am not promoting looping constructions in SQL programming, but if it happens, repeatedly creating and dropping the temp table will be very slow. If the proc only creates the table, the table contents will be dropped when the stored proc ends, which effectively means that the temp table does not exist anymore. However, the temp table structure (the definition of its columns) will be cached. When the loop calls the stored proc again, the create statement will be much faster because the table structure will be read from cache. There is a drawback, however. If you use temp tables often, and if you always call them #t, but with a different structure in different procs, it might happen that firstly proc 1 is called, which results in the structure of #t being cached as defined in proc 1, and then proc 2 is called. When proc 2 tries to create #t, the definition of #t from proc 1 is read from cache, notwithstanding the different definition in proc 2. Since the structure of #t is different in proc 2, an insert statement will probably fail. This is not theoretical, I have seen it happen. It costed me half a day to find out what caused the problem. For me, at first, it was flabbergasting because when I read the code of proc 2, it said something like:

    CREATE #t (i int, streetname nvarchar(50));
INSERT INTO #t VALUES (1, 'Fleetstreet');

    When you see code like that, and you don't know about the phenomenon above, an error telling you that the column 'streetname' does not exist, does not seem to make much sense.

    So if you want to speed up repeatedly creating and dropping the same temp table, DO NOT explicitely drop the temp table in the proc. Make sure, however, that you use proper names for your temp tables (#customer_addresses instead of #t) and that a certain name always corresponds to the same definition. You could prefix the name of the table with the stored proc name, for instance.

    If you don't have while loops but proper set oriented T-SQL, and if your client app doesn't call the same proc from a loop, DO explicitely drop the temp table in the proc. The overhead in performance won't be to bad and you don't run into the problem described above.

    But then, this is my humble opinion. There people posting on this forum who know a lot more about MS SQL Server than I do. People like Naomi and Erland, for instance.


     
  • Friday, February 22, 2013 12:34 AM
     
     
    Sign In to Vote
    0

    Hi there,

    interesting topic.

    There is no need to write a command to drop the temp or table variables. They will be dropped  as soon as the connection closes.

    They have same lifespan like any other variables in T- SQL. they live only the connection is active.

    i used to write code as your friend said. i stopped doing that long time ago. never have an issue.

    i would think about GAM or SGAM if the temp object size is more than 4GB in size.

    cheers

    kumar

     
  • Friday, February 22, 2013 9:55 AM
    Moderator
     
     
    Sign In to Vote
    0

    GYP (Get Your Point), Gert-Jan.

    The perfectionist in me has no objections whatsoever with you viewpoint. I haven't seen or myself tried to quantify the gains with caching one page for temp tables - it seems the gain would be for high transaction rate and possibly smaller tamp tables situations.

    I believe it was in 2008, but I won't swear on it. Possibly 2005...

    Tibor Karaszi, SQL Server MVP | web | blog