Stored Procedures Best Practice Temp / Variable Table versus Physical table

Question

Hi:

We have some old stored procedures and we noticed that whoever wrote this had limited knowledge of database. They are dropping and recreating physical tables (TMP_...) within stored procedures instead of using temp tables (#) or table variables. Is there any advantage of using physical table vs temp or variable tables?

Is there any security risk and Performance issue?

Before we give any recommendation for making changes to those stored procedures I wanted to know from experts here what we are going to propose is right solution and why recreating physical table for temporary storage is bad idea.

Thanks!

Answer 1

Hi @SQL

Physical tables are typically created to store application data. They store all the persistent data that the application needs.

Temporary tables are used to store temporary information. Most of the time, you don't need to keep your data after you've processed it. For example, a stored procedure might store intermediate results in a temporary table and process them for better performance.

In your case, you need to drop and rebuild the table. So using physical tables is not appropriate.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

There can be special scenarios where you want to do this. The chief reason would be that you want the data to survive crashes, so that you can continue where the server crashed without having to start over. But this is a pattern you should be able to identify quickly.

I have also used permanent tables for temporary data, when I want to pass data between stored procedures, so that the table definition is only in one place. But in this case, you would not drop and re-create the table in the procedure, but you would do it once for all.

So, yes, unless you can identify the situations I described above, it is definitely a correct call to replace the permanent table with a temp table. Temp table generally gives better performance, since SQL Server don't need to write equally much to the transaction log. (Since tempdb does not need to be recovered when the server starts up.) From a seucity perspective is also not a good thing to drop and create tables, since this means that the user running the procedure needs to have these permissions.