Temp Table

Last Post 10 Feb 2011 07:26 AM by gunneyk. 11 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
26 Jan 2011 03:52 AM
Hi Masters,

    Some of the people (programmers) that work in my department when creating a temporary table to do something, they create like this:


     create table tablename


Then, when they do not need to use this tbale they destroi it.

  like:    drop table tablename



Isn't more correct to create  a temp table like :    create table #tablename  or create table  ##tablename?


I think that one of this to methods is better, then use the first, can you masters explain the reason why is better to use the create table #tablename instead of creating a normal table?


I think that, it is because when i create a normal table it will create infromation on the log of the database. When i drop the table the same thing appens.

But if i create a real temp table (#) the information will only be written in the Temp database.

Am i correct?


Thank you

rm
New Member
New Member

--
26 Jan 2011 06:57 AM
Depends on what the table for and scope of availability, sql will log those in either tempdb or user db.
river1
New Member
New Member

--
30 Jan 2011 08:59 AM
sorry master RM didn.t understood your answer...
gunneyk
New Member
New Member

--
30 Jan 2011 12:47 PM
Both a real table and temp table log information in the tran logs but the temp table will log in the tempdb log vs. the user db log. The biggest problem with a real table is that if the name is the same as someone elses temp table there will be a problem. If they need a real table for some reason that is only temp in nature they might want to consider creating a staging type database that is used for that purpose and then all that work will not be in the production db. Keep that one in SIMPLE mode.
river1
New Member
New Member

--
31 Jan 2011 12:52 AM
Thank you.

I just didn't understood yet, what you mean with : Both a real table and temp table log information in the tran logs but the temp table will log in the tempdb log vs. the user db log

Can a real temp table (#tablename) log information into the transaction log of a user database? in wich conditions?


Thnka you once again.
russellb
New Member
New Member

--
31 Jan 2011 04:23 AM
No. a #temp table will use tempdb log
gunneyk
New Member
New Member

--
31 Jan 2011 06:11 AM
I meant that they both log their actions but a #table will do it in the tempdb log file where as a real table that is created in a user db will log inthat user dbs log file.
russellb
New Member
New Member

--
31 Jan 2011 11:01 AM
Pedro, it may help to understand that all #temp tables live in tempdb.
river1
New Member
New Member

--
08 Feb 2011 04:00 AM
Yes Master Russellb , that i know.

That's why i don't understand why people create "temp tables" as normal tables ..... instead of creating a real temp table that will only log infromation into temp DB log. Can you see any advantage on that? i only see disadvantages
gunneyk
New Member
New Member

--
08 Feb 2011 12:18 PM
The temp table only stays around until the session is done. If you need a temporary type table to persist for a certain period of time you have to create a real table. That is really the only reason to do so. Usually it is done by people who don't understand SQL Server.
russellb
New Member
New Member

--
09 Feb 2011 07:43 PM
Speaking of which, I have never, ever found the need to create a ##GlobalTemp. Have you guys?
gunneyk
New Member
New Member

--
10 Feb 2011 07:26 AM
Not any legimitate reasons for global temp tables
You are not authorized to post a reply.

Acceptable Use Policy