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 implicit transactions in temp table

I have a .net program which usually create temp table in SQL Server 2005 and
populate it from com contrlol to apply later to the main table.But sometimes
I need to delete some of previously inserted rows to replace them with maybe
more and adjusted ones.
I did this by deleting suspicious rows and going back on com control to
inserting rows.
I did found that I am missing some rows.
It seems that deletion does not synchronized with consequenced insertions.
Is it neccessary to do explicit transaction or this is one of 2005
optimizations on temp tables?Hi
I am not really clear how you are doing this, maybe you would like to post
some sample code? If the com control has it's own connection then you may no
t
be using the same temporary table!
John
"UncleSam89" wrote:

> I have a .net program which usually create temp table in SQL Server 2005 a
nd
> populate it from com contrlol to apply later to the main table.But sometim
es
> I need to delete some of previously inserted rows to replace them with may
be
> more and adjusted ones.
> I did this by deleting suspicious rows and going back on com control to
> inserting rows.
> I did found that I am missing some rows.
> It seems that deletion does not synchronized with consequenced insertions.
> Is it neccessary to do explicit transaction or this is one of 2005
> optimizations on temp tables?|||It seems that it was my own bug
sorry
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I am not really clear how you are doing this, maybe you would like to post
> some sample code? If the com control has it's own connection then you may
not
> be using the same temporary table!
> John
> "UncleSam89" wrote:
>

About implicit transactions in temp table

I have a .net program which usually create temp table in SQL Server 2005 and
populate it from com contrlol to apply later to the main table.But sometimes
I need to delete some of previously inserted rows to replace them with maybe
more and adjusted ones.
I did this by deleting suspicious rows and going back on com control to
inserting rows.
I did found that I am missing some rows.
It seems that deletion does not synchronized with consequenced insertions.
Is it neccessary to do explicit transaction or this is one of 2005
optimizations on temp tables?Hi
I am not really clear how you are doing this, maybe you would like to post
some sample code? If the com control has it's own connection then you may not
be using the same temporary table!
John
"UncleSam89" wrote:
> I have a .net program which usually create temp table in SQL Server 2005 and
> populate it from com contrlol to apply later to the main table.But sometimes
> I need to delete some of previously inserted rows to replace them with maybe
> more and adjusted ones.
> I did this by deleting suspicious rows and going back on com control to
> inserting rows.
> I did found that I am missing some rows.
> It seems that deletion does not synchronized with consequenced insertions.
> Is it neccessary to do explicit transaction or this is one of 2005
> optimizations on temp tables?|||It seems that it was my own bug
sorry
"John Bell" wrote:
> Hi
> I am not really clear how you are doing this, maybe you would like to post
> some sample code? If the com control has it's own connection then you may not
> be using the same temporary table!
> John
> "UncleSam89" wrote:
> > I have a .net program which usually create temp table in SQL Server 2005 and
> > populate it from com contrlol to apply later to the main table.But sometimes
> > I need to delete some of previously inserted rows to replace them with maybe
> > more and adjusted ones.
> > I did this by deleting suspicious rows and going back on com control to
> > inserting rows.
> > I did found that I am missing some rows.
> > It seems that deletion does not synchronized with consequenced insertions.
> > Is it neccessary to do explicit transaction or this is one of 2005
> > optimizations on temp tables?