x

Dropping a temp table

I've seen three different sets of syntax for dropping temp tables as a precaution before creating the same temp table, and I'm just wondering why you'd choose one method over another. They are:

METHOD 1

 IF OBJECT_ID('tempdb..#some_temp_tbl') IS NOT NULL
   BEGIN
     DROP TABLE #some_temp_tbl;
   END

METHOD 2

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

METHOD 3

 DROP TABLE #some_temp_tbl;

I presume method three means "We accept that we might generate the [Cannot drop the table '`#some_temp_tbl`', because it does not exist or you do not have permission.] error, but there's no harm done. Is it reasonable to assume that no harm will be done? As for methods 1 and 2, why would you choose one over the other?

more ▼

asked Aug 24, 2010 at 05:56 PM in Default

avatar image

GPO
4.7k 40 49 56

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Options 1 & 2 are basically the same. The BEGIN/END doesn't change anything. Some people just put them in automatically when dealing with IF statements. Since the IF statement will work with the very next statement, a BEGIN allows for more than one statement, but the option without is just doing the same thing.

more ▼

answered Aug 24, 2010 at 06:18 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

Thanks Grant. Is there any problem with dispensing with the IF statement altogether and just saying DROP TABLE (as in method 3)?

Aug 24, 2010 at 07:44 PM GPO

It depends (I love that answer). If I was writing the code to create the temporary table, I could just drop without doing a check to see if it existed first. If, on the other hand, it's part of a more extensive set of code and I didn't know, for sure, that the temp table would exist when I called the DROP statement, no, I'd leave the IF in place.

Aug 25, 2010 at 04:35 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

I agree with Grant. As you probably already know, if you try to drop a temp table or another object that doesn't exist you will trigger an exception / error. In SQL 2005 or later, you can use BEGIN TRY instead of the IF statement. I am not sure about the performance implication of this solution, but it gives you the option to handle other exceptions that might show up.

 BEGIN TRY
     DROP TABLE #some_temp_tbl;
 END TRY
 BEGIN CATCH
     -- take care of the exception, you can even throw the message to the caller if you like
     PRINT ERROR_MESSAGE();
     PRINT ERROR_NUMBER();
 END CATCH

IF statements (and heavy use of temptables) in a stored procedure may cause a recompile on every call, and that is not an option if you have thousands of requests / s

more ▼

answered Aug 24, 2010 at 11:50 PM

avatar image

Håkan Winther
16.4k 36 45 57

Thanks Håkan,

The question is not so much about whether the error will be triggered, more about what the implications of triggering the error are. In SSMS it doesn't stop the subsequent code executing. Would this be the same where the code was executed by other processes? If there's no downside, why bother with the IF statement? Yes the error message exists, but is it of any consequence? I think that's what I'm getting at.

Aug 25, 2010 at 12:48 AM GPO

SQL will continue the execution directly after the END CATCH block or directly after your statement if you don't use the TRY/CATCH syntax as you pointed out. But keep in mind that you could have a calling procedure that has a TRY/CATCH block, if it does, the exception will be caught and handled in that procedure. I would use the CATCH syntax to be able to trap all exceptions, just to be on the safe side.

Aug 25, 2010 at 01:32 AM Håkan Winther

"...keep in mind that you could have a calling procedure that has a TRY/CATCH block..." Ah! That's the sort of thing I'm after. Greatly appreciated Håkan.

Aug 25, 2010 at 01:53 AM GPO
  • Good point.

Aug 25, 2010 at 04:36 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Method 1 and Method 2 are functionally equivalent. However, the real difference between them is important as it is a matter of maintenance. Consider that over the course of several years there will likely be changes made to this code, and likely by someone who did not code it originally. So the desire might be to add a flag for some reason to indicate that this operation needed to be performed (just a contrived yet simple example). So the change would be to add a BIT variable for @DroppedTempTable = 0 and a SET @DroppedTempTable = 1; statement if that IF condition evaluates to "true". That should look like:

 DECLARE @DroppedTempTable BIT = 0;
 
 IF (OBJECT_ID(N'tempdb..#some_temp_tbl') IS NOT NULL)
 BEGIN
    DROP TABLE #some_temp_tbl;
    SET @DroppedTempTable = 1;
 END;

Real easy-like that change was :-). But what if the original code didn't have the functionally-superfluous BEGIN / END markers and Over-Worked-Developer-With-Less-Than-Stellar-Attention-To-Detail makes the change quickly, 5 minutes after the sprint ends and the Release Engineering team has declared "Code Freeze" so that they can push the changes to Staging, and Over-Worked-Developer does not want to go ask a manager to intervene so that this one last change can get in. That could look like:

 DECLARE @DroppedTempTable BIT = 0;
 
 IF (OBJECT_ID(N'tempdb..#some_temp_tbl') IS NOT NULL)
    DROP TABLE #some_temp_tbl;
    SET @DroppedTempTable = 1;

Guess what? The SET statement will always execute since it is not tied to the IF statement. SQL Server doesn't care about indentations or intention ;-).

The moral of this story: Yes, the BEGIN / END tags are functionally extraneous on IF and WHILE statements when the action is a single statement/query. HOWEVER, at the same time, those BEGIN / END tags will never be functionality incorrect! And having them there almost guarantees that any updates made in the future to include more than 1 action dependent upon the IF being "true" will be done correctly the first time, thereby cutting down on easily-avoidable wasted QA time and code fixing and redeployment time.

Having the BEGIN / END tags also assists in readability for someone quickly scanning the code when trying to determine what it is doing (either for support or scoping out new functionality and looking for what needs to change, etc). Assume that the following code is correct:

 DECLARE @DroppedTempTable BIT = 0;

 IF (OBJECT_ID(N'tempdb..#some_temp_tbl') IS NOT NULL)
 DROP TABLE #some_temp_tbl;
 SET @DroppedTempTable = 1;

 EXEC dbo.SomeProc;

How many people, looking at that real quickly, especially due to where the blank lines are, would misread that and think that that the DROP and SET are both dependent upon the IF? Certainly some. But adding in the functionally extraneous BEGIN / END tags would almost guarantee that nobody would misread it. That is certainly worth the time it takes to add in those 8 extra characters, plus the 2 extra returns, but some spaces and/or tabs ;-).

Regarding Example 3 and not having the IF check at all, that is just bad / sloppy coding. It doesn't really matter if typically SQL Server would not halt execution on the error. Clean, good code should handle errors appropriately. What if someone adds SET XACT_ABORT ON; above that code, in the same proc or a calling proc? What about app code that might be trapping a SqlException? I don't think such an error would merely look like a PRINT statement (i.e. informational only). Regardless, knowing that an error can occur but intentionally not dealing with it because execution at least continues is just bad coding. Exceptions happen, and they should be handed, even if that handling is to merely swallow them and move on to the next statement. Ignoring the error should be intentional and not merely a side-effect.

more ▼

answered Oct 30, 2015 at 03:51 PM

avatar image

srutzky
488 3 6

(comments are locked)
10|1200 characters needed characters left

You can also try this

 IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tablenm')) 
 BEGIN 
   DROP TABLE #tablenm 
 END
more ▼

answered Aug 25, 2010 at 06:09 AM

avatar image

V Padmala
83 2 3 6

@V Padmala But that would be a bit too expensive. object_id check already does the trick, so there is no reason to issue a select. The actual reason to use the object_id is based on the simple fact that the actual table name (queried from sys.tables of the tempdb itself) is not equal to the name which was used to create it. Theoretically, it is always possible to drop the table by switching context to tempdb and then dropping the table by its actual name, but that would be dumb and dangerous as it allows dropping temp table when it is actively used by a different process (drop the wrong table in other words). What I mean is that if there is a proc which creates temp table and the proc is called by 5 sessions then there are 5 tables in tempdb, each with different (actual) name. object_id check allows checking existence of just the table which was (or not) created by the same session.

Just my 2 cents

Aug 25, 2010 at 08:08 AM Oleg

Also keep in mind that dbo.sysobjects is deprecated and will be dropped in future releases of SQL server. It is replaced by sys.objects

Aug 25, 2010 at 01:42 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066
x42
x31
x15

asked: Aug 24, 2010 at 05:56 PM

Seen: 9739 times

Last Updated: Oct 30, 2015 at 03:51 PM

Copyright 2016 Redgate Software. Privacy Policy