Temp Table v/s Permanent table for temporary process?

Hi, In our application for business process developer create permanent table instead of #table and process temporary data in this table and drop this table again in same scope and some time leave this in database and next time drop and recreate for temporary process.

So I want to know what are the strong disadvantages of using permanent table instead of actual temp table for process the temporary data for same scope?

more ▼

asked Jul 05, 2011 at 02:29 AM in Default

Amardeep gravatar image

1.3k 87 88 89

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

4 answers: sort voted first
Use of a temporary table makes it obvious to all programmers / dbas that it is a temporary table. Makes for easier maintainability.
more ▼

answered Jul 05, 2011 at 02:39 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

I am hoping some more disadvantages on DB storage data file fragmentation and performance point of view?
Jul 05, 2011 at 02:47 AM Amardeep
(comments are locked)
10|1200 characters needed characters left

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).
more ▼

answered Jul 05, 2011 at 05:49 AM

Blackhawk-17 gravatar image

11.9k 28 31 37

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

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.
more ▼

answered Jul 05, 2011 at 05:22 AM

sp_lock gravatar image

9.3k 25 28 31

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Jul 05, 2011 at 09:32 AM

Tim gravatar image

36.4k 38 41 139

(1) Does it will increase file fragmentation ? (2) Does it will create issue if two or more session try to process same application process? (3) Drop and recreate will be faster or slow for permanent table in compare with temp ? (4) select or insert will be faster or slow with permanent table in compare with temp?
Jul 05, 2011 at 10:11 PM Amardeep
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jul 05, 2011 at 02:29 AM

Seen: 2685 times

Last Updated: Jul 05, 2011 at 02:29 AM