Temp Table v/s Permanent table for temporary process?

Use of a temporary table makes it obvious to all programmers / dbas that it is a temporary table. Makes for easier maintainability.

Good question.

It depends on your system's configuration and usage.

Here's a couple of thoughts on each:

A permanent table may end up with heavy blocking issues as simultaneous use from different sessions is underway. A permanent table's indexes are in place and don't need to be recreated.

Temporary tables live in temp dB and this is a highly accessed area which may lead to I/O contention. Each session has their own version of the table to work from and blocking is almost non-existent for the table as a result.

Depending on the size of result sets you are working with you might consider table variables instead (version dependent).

Things to consider where the staging data is stored, temps at store in the well-know place but the "perm" are actually stored in the DB it self. So if your tempdb is on a different array and isnt overloaded then at could be a reasonable choice..

Depending on the size there shouldnt be too much difference. One decent reason to have a perm table is for reviewing reason, maybe.

I would personally test the performace before has, as things like this differ from system to system.

I know there are pro's and con's to both but I prefer staging tables in a separate database or in the one I am working on. If I am only working with a small dataset I may use a temp table but usually use a CTE if I can get away with it. I don't like adding any additional pressure on TEMPDB if I can help it. As others have pointed out it really all depends on your current need. One of the things I like about a perm table is it also allows you the ability to not have to rebuild that dataset if something in your process breaks further down stream.