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.

About managing SQL server 2005

Hi, I just start using MSSQL server 2005, I already install vss studio2005 so SQL server 2005 is already installed by default (expressedition).

But unlike SQL server 2000, I coultn't find any tools similiar to "sqlentreprise manager". I was able to create a new database only fromserver explorer tools in VSS. But pretty strange that this database actlike a microsoft access database (creating a database file on myapp_data folder). (I am sorry I am new in SQL server 2005ExpressEdition ).

My question :
1. is that (above) really how mssql server 2005 works?
2. how to create a new user for this database? (example user: sa password: "admin")

thanks

**BUMP**

Please help me.....

|||

Test drive the eval version is it good for 180 days install it as a named instance and register the Express so you can manage it with the management studio of the full version. I have not used the Express but the full version almost self install after you select the components to install, then right click at the top of management studio to register the Express. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx