Dropping Temp Table in Stored Procedure – SQL in Sixty Seconds #124

Dropping Temp Table in Stored Procedure - SQL in Sixty Seconds #124 124-TempTableDrop-part-YT-800x450 Is it necessary to drop a temp table created in the stored procedure? I was recently asked this question during the Comprehensive Database Performance Health Check. Let us learn that today with the help of a video.

Lots of people think that all the temp tables created inside the stored procedures should be dropped and many often say, that dropping will take extra CPU cycles and it should be ignored. I am sure just like many people out there, you may have wondered this question as well and it is quite possible that you have also tried out various experiments with it.

During the course of my consultancy, I have been facing this question pretty much at the regular interval. I have explained my findings in the SQL in the Sixty Seconds video here.

In this video, I do a simple demonstration of running two stored procedures. In one stored procedure, I create a temp table and drop it and in the second one, I only created a temp table but do not drop it. I run both the stored procedure for 5000 times and compare their performance.

Honestly, I have run many different stored procedures and many different times. Every single time, I have realized that both the scenario are comparable to each other and give very similar performance.

Here is the code which I have used in the video.

CREATE OR ALTER PROCEDURE TempDrop
AS
CREATE TABLE #TempTable (ID BIGINT)
INSERT INTO #TempTable (ID)
VALUES (1)
DROP TABLE #TempTable;
GO
CREATE OR ALTER PROCEDURE TempNoDrop
AS
CREATE TABLE #TempTable (ID BIGINT)
INSERT INTO #TempTable (ID)
VALUES (1)
-- DROP TABLE #Temp1;
GO

Now you may wonder what is my actual preference in this scenario. To know my preference, you will have to watch the video.

If you are wondering why it is not required to drop the temp table at the end of the stored procedure, well, it is because when the stored procedure completes execution, it automatically drops the temp table when the connection/session is dropped which was executing it.

Well, that’s it. You may subscribe to my YouTube channel here.

Reference: Pinal Dave (https://blog.sqlauthority.com

, , , , , ,
Previous Post
25 Recent SQL in Sixty Seconds Video
Next Post
Nested Transactions are Myth – SQL in Sixty Seconds #125

Related Posts

11 Comments. Leave new

  • I’ve always thought that a key reason for dropping them was because they live for the duration of the session. Many applications use session pools and sessions last for ‘a while’ and may execute many queries.

    Reply
  • Only within scope… when the proc ends, that scope ends, which is why they are dropped implicitly if you don’t drop them explicitly. A session may last hours, but if the call runs for 10 ms, that table only exists for up to 10 ms.

    Reply
  • During development and testing, re-running a stored procedure in SSMS is an issue unless I open a new query window for each run of the procedure. Since doing an explicit drop adds negligible time to the execution and none of my procedures get run frequently enough to add up to something measurable, I’ll leave the drops in my procedures. Your mileage may vary.

    Reply
  • Not sure if anyone here has been through this but, I usually check for previous existence of a temporary table when creating them inside a stored procedure but I also give them unique names across stored procedures.

    — check for temporary table existence
    IF OBJECT_ID(‘TEMPDB…#CAMPAIGN_LIST’) IS NOT NULL
    DROP TABLE #CAMPAIGN_LIST

    Why is this?
    As an example, I used to name my temporary tables as #TEMP (great name right? :-)). Whenever I created other stored procedures that used temporary tables, I named them the same way (#TEMP). So whenever I executed a stored procedure which used temporary tables, and then executed another one (that also used temporary tables), the 2nd stored procedure threw an error stating that another temporary table already existed with the same name. Of course this usually happened while the 1st stored procedure was executing. If the 1st stored procedure had finished executing… no error was thrown from the 2nd stored procedure.

    So I applied the following solution which is… prepending the name of the stored procedure and the purpose of the temporary table.

    #_

    For example:
    –INITIAL NAMES (the bad way):
    uspLeads is the name of the stored procedure
    #CAMPAIGN_LIST internal temporary table in one store procedure (here i don’t have the name of the stored procedure prepended)

    uspResponses is the name of the stored procedure
    #CAMPAIGN_LIST internal temporary table in another stored procedure (here i don’t have the name of the stored procedure prepended)

    –SOLUTION (the good way):
    uspLeads is the name of the stored procedure
    #uspLeads_CAMPAIGN_LIST internal temporary table in one store procedure

    uspResponses is the name of the stored procedure
    #uspResponses_CAMPAIGN_LIST internal temporary table in another stored procedure

    So, again for checking the previous existence (as an example)…
    — check for temporary table existence
    IF OBJECT_ID(‘TEMPDB…#uspLeads_CAMPAIGN_LIST’) IS NOT NULL
    DROP TABLE #uspLeads_CAMPAIGN_LIST

    At least this has worked for me but any comments/suggestions are more than welcome.

    Thanks,

    Reply
  • One thing I was told by a SQL consultant is that an explicit drop also clears the table’s statistics, which could potentially lead to less efficient plans. Is that not true?

    Reply
    • Just try out based on the demo which I have shared. There are so many things that we need to learn and verify.

      As far as I know, the tables are dropped in both the cases and I have not seen many changes in table statistics.

      Reply
  • Pinal, for a comprehensive education on temp tables in a procedure, might I suggest you demonstrate the importance of the transactions on the tempDB? If you have a great deal of data in those temp tables, truncating rows before dropping table objects could be a desirable effort in some cases. Especially for the cases that come up pertaining to what Russ & Michael have mentioned.

    Reply
  • This would be OK, as long as the same SP isn’t called inline or synchronously. If the probability instance is low, it may be safe. But putting in an existence check only allows you to handle the error inside the proc. It doesn’t aquaint the proc with an alternative intellegence. The question to present to the project is how much impact or probability of downstream impact could be caused by the table error occurance? Evaluating for the frequency and introducing a judicial approach is the best practice.

    Reply
  • Michael D Ballard
    September 29, 2020 6:02 pm

    Meaningful and unique names in stored procedures are a great help to the person who has to come along a month or a year or five years later and figure out what you where thinking when you wrote the procedure originally. “#CustomerIDsToBeDeleted” is a bit of extra typing but vastly more useful than “#Temp4”. I’ve even seen procedures with “#X”, “#X1”, … , “#X14”. When the procedure is hundreds or thousands of lines long, meaningful names can be a huge help to keeping track of what’s in each one. It also reduces the likelihood of repeating the same name when your procedure calls another one.

    Reply

Leave a Reply

Menu