SQL Server - Where is temp table stored? - Asked By theiry henry on 11-Mar-10 05:52 AM

When u create a temp table .Where actually is it stored?plz reply its urgent..
Sreekumar P replied to theiry henry on 11-Mar-10 05:57 AM
hi,

yes U can create.

A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:

http://www.sqlteam.com/article/temporary-tables
Ramendra Kumar replied to theiry henry on 11-Mar-10 05:59 AM

By default, the database server stores temporary tables in the root dbspace. If you decide not to store your temporary tables in the root dbspace, use the DBSPACETEMP environment variable or the DBSPACETEMP configuration parameter to specify a list of dbspaces for temporary tables.

Temporary Tables That You Create
You can create temporary tables with any of the following SQL statements:

TEMP TABLE option of the CREATE TABLE statement
INTO TEMP clause of the SELECT statement, such as SELECT * FROM customer INTO TEMP cust_temp
Only the session that creates a temporary table can use the table. When the session exits, the table is dropped automatically.

When you create a temporary table, the database server uses the following criteria:

If the query used to populate the TEMP table produces no rows, the database server creates an empty, unfragmented table.
If the rows that the query produces do not exceed 8 kilobytes, the temporary table resides in only one dbspace.
If the rows exceed 8 kilobytes, the database server creates multiple fragments and uses a round-robin fragmentation scheme to populate them unless you specify a fragmentation method and location for the table.
If you use the CREATE TEMP and SELECT...INTO TEMP SQL statements and DBSPACETEMP has been set:

LOGGING dbspaces in the list are used to create the tables that specify or imply the WITH LOG clause.
NON-LOGGING temporary dbspaces in the list are used to create the tables that specify the WITH NO LOG clause.
When CREATE TEMP and SELECT...INTO TEMP SQL statements are used and DBSPACETEMP has not been set or does not contain the correct type of dbspace, Dynamic Server uses the dbspace of the database to store the temporary table

Sagar P replied to theiry henry on 11-Mar-10 06:00 AM
Temporary tables (local and global) are stored in the tempDB database.

When you declare a temporary table, SQL Sever adds some additional characters on its name in order to provide a unique system name for it and then it stores it in tempDB in the sysobjects table. Even though you can query the temporary table with its logical name, internally is known with the exact name SQL Server has set.

http://aartemiou.blogspot.com/2009/04/where-are-temporary-tables-stored-in.html
Ramendra Kumar replied to theiry henry on 11-Mar-10 06:01 AM
If u need more help or guide line follow this link
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin365.htm
mark as flag if u got help
Kirtan Patel replied to theiry henry on 11-Mar-10 06:38 AM
Temporary Tables are Created In main Memory ..and It will be Destroyed as the SQL Server Session Ends...

There is Restriction on TEMP table that you can not store Foreign key etc in it ..
Jonathan VH replied to theiry henry on 11-Mar-10 07:45 AM
In case you're confused, the first reply (from "Sreekumar P") is correct.
Super Man replied to theiry henry on 11-Mar-10 07:55 AM

 

 

 

The answer is that temporary tables (local and global) are stored in the tempDB database