3

When using temp tables in SQL Server stored procs, is the preferred practice to;

1) Create the temp table, populate it, use it then drop it

CREATE TABLE #MyTable ( ... )

-- Do stuff

DROP TABLE #MyTable

2) Check if it exists, drop it if it does, then create and use it

IF object_id('tempdb..#MyTable') IS NOT NULL
DROP TABLE #MyTable

CREATE TABLE #MyTable ( ... )

3) Create it and let SQL Server clean it up when it goes out of scope

CREATE TABLE #MyTable ( ... )

-- Do Stuff

I read in this answer and its associated comments, that this can be useful in situations where the temp table is reused that SQL Server will truncate the table but keep the structure to save time.

My stored proc is likely to be called pretty frequently, but it only contains a few columns, so I don't know how advantageous this really is in my situation.

Community
  • 1
  • 1
Mr Moose
  • 5,946
  • 7
  • 34
  • 69
  • Or create table implicitly using `SELECT`/`INTO`. – HABO Sep 06 '12 at 14:50
  • 1
    @HABO how does that help? If the table already exists, an error will occur, and the question is still about whether it needs to be dropped - why would that question go away if the table was created with SELECT INTO? – Aaron Bertrand Sep 06 '12 at 15:03
  • @AaronBertrand - Sorry, I thought I saw the word "creating" in the title. Knowing your penchant for completeness I felt that raising the issue of `SELECT`/`INTO` was not entirely off topic. Once again, mea culpa. – HABO Sep 06 '12 at 15:24

3 Answers3

2

You could test and see if one method outperforms another in your scenario. I've heard about this reuse benefit but I haven't performed any extensive tests myself. (My gut instinct is to explicitly drop any #temp objects I've created.)

In a single stored procedure you should never have to check if the table exists - unless it is also possible that the procedure is being called from another procedure that might have created a table with the same name. This is why it is good practice to name #temp tables meaningfully instead of using #t, #x, #y etc.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • My temp table is called from another one as you suggest. It can get called any number of times in relatively quick succession. I used to opt for method 1, but was always concerned that in error situations, could I end up without the drop being called. I only recently came across method 2, and that is what I am using at the moment. It works fine, but I just wanted to make sure it isn't considered bad practice for some reason. – Mr Moose Sep 06 '12 at 14:58
  • 1
    Even in an error condition, if the stored procedure aborts, the stored procedure exits, and the table is dropped. You could always put the create table inside TRY and drop table inside CATCH, which will handle most error conditions more explicitly (but I still think your concern is unfounded). – Aaron Bertrand Sep 06 '12 at 14:59
  • 1
    Thanks. I'm sure a lot of my concerns are unfounded. I'm a worry wart :) – Mr Moose Sep 06 '12 at 15:02
1

I follow this approach:


IF object_id('tempdb..#MyTable') IS NOT NULL

DROP TABLE #MyTable

  CREATE TABLE #MyTable ( ... )

  // Do Stuff

IF object_id('tempdb..#MyTable') IS NOT NULL

DROP TABLE #MyTable

Reason: In case if some error occurs in sproc, and created temp table is not dropped and when the same sproc is called with check for existence, it will raise error that table cannot be created, and will never get successfully executed unless the table is dropped. So always perform check for the existence of and object before creating it.

Jitendra Gupta
  • 764
  • 1
  • 8
  • 16
0

When using temp tables my preferred practice is actually a combination of 1 and 2. 

  IF object_id('tempdb..#MyTable') IS NOT NULL
     DROP TABLE #MyTable

  CREATE TABLE #MyTable ( ... )

  // Do Stuff

  IF object_id('tempdb..#MyTable') IS NOT NULL
     DROP TABLE #MyTable
Durus
  • 136
  • 6