About TempDB

TempDB is one of the databases equipped with MSSQL Server by default.
What is the purpose of it?
Why do we use this temp database?

A) Any time you create a temporary table, it's actually created in tempdb:
--Create a temp table
CREATE TABLE #TempTable112233 (somecol INT)
--You can now see it in tempdb's sysobjects table
SELECT *
FROM tempdb..sysobjects
WHERE NAME LIKE '#TempTable112233%'
B) Many operations in SQL Server require temporary/intermediate tablesand/or sorts. For instance, if you select some data from twotables, part of the data from one of the tables may be sorted beforebeing joined with the data in the other table. That sort canoccur in tempdb.

|||The Temp DB in SQL Server 2000 is used for complex intermediate operations and can also be used to as the database to test stored procs if there is no database and tables to test them against because you don't need a database or tables to create stored procs, assuming the feature was not altered with a service pack. There two types of temp tables local with one # sign and global with two ##, the local is the most used but if your operation is long the global is more durable but it has performance impact so you have to know when to use them. Hope this helps.|||I understand from our DBA that tempdb is used to generate a templatefor a new database? Just that I caught a bit of a wigging forcreating a table without the # prefix to its name in that location, andwas told that the tempdb contributed the layout of new databases andother important things.
So what I should like to know is -- is tempdb purely a scratch area, or is it used for several other purposes?
--
My Alias

|||

My Alias wrote:

I understand from our DBA that tempdb is used to generate a template for a new database? Just that I caught a bit of a wigging for creating a table without the # prefix to its name in that location, and was told that the tempdb contributed the layout of new databases and other important things.
So what I should like to know is -- is tempdb purely a scratch area, or is it used for several other purposes?
--
My Alias

Your DBA is wrong Model DB is the template while TempDB is an intermediate or staging place to create tables and run code. Hope this helps.