Temp Tables vs. Real Tables 1

[mike2312]

I am having difficulty explaining to a co-worker why the use of real tables, instead of temporary tables or a table variable, for temporary use (just for a stored procedure) is a bad, bad thing. Could anyone here give me ammo.

 

[bhuninghake]

Take a look at this:

http://dotnetjunkies.com/WebLog/ekepes/articles/13938.aspx

 

[mike2312]

Thanks for the reply bhuninghake. That is a good article but not quite what I am looking for. I am looking for something that says that if you use real tables instead of temp tables of table variables for temporary work that it is a very bad thing.

 

[JamesLean]

The main problem with using permanent tables is when two users try and run the SP at the same time. You either get an error when you try and create the table saying it already exists or if you have a DROP TABLE at the start then you cause errors with the first user that is still trying to access the table.

When using a temp table (or preferably a table variable) the table is only visible to the connection that created it, so multiple users can run the SP at the same time and all create a table called #test without affecting the other users.

Also, temp tables will automatically get dropped at the end of the procedure so you don't get tables hanging around in the database after you're finished with them.

--James

 

[mike2312]

James,
Thanks for the response. This developer has made it so the tables are named dynamically. So there will be no contention if the SP is run by multiple users.