Different between ##table and #table regarding performance

hi
also i want to know what have more performance : a table variable or a
temporary table in a stored procedure
the rows in the tables are approx. 1000
thanks michelINF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/?kbid=305977
AMB
"haenselmic" wrote:

> hi
> also i want to know what have more performance : a table variable or a
> temporary table in a stored procedure
> the rows in the tables are approx. 1000
> thanks michel|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:0E5B6748-F110-497E-ABE3-C9B90F6280FA@.microsoft.com...
> INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
> http://support.microsoft.com/?kbid=305977
>
> AMB
> "haenselmic" wrote:
>
Also I have never found a valid use for an ##temp global temporary table.
David|||You'll generally get better performance from in-memory temporary tables as
long as they are fairly small. SQL Server will push the memory table to
tempdb in certain situations such as row count or available memory. There
are some limitations on indexing with in-memory temporary tables. BOL has a
good section on temporary tables and explains better than I can without
plagairism <g>.
## temporary tables are 'global' temporary tables and are cleaned up when
sql server recycles tempdb. # temporary tables are local to the current
batch and are removed when the batch has completed.
-TIm
"haenselmic" <haenselmic@.discussions.microsoft.com> wrote in message
news:273D07B3-0236-43DC-AEF6-8F6D25681AAA@.microsoft.com...
> hi
> also i want to know what have more performance : a table variable or a
> temporary table in a stored procedure
> the rows in the tables are approx. 1000
> thanks michel|||I have used them a couple of times. It has been a while. From what I can
remember we had to dynamically build several sql statements, insert them
into one (##temporary) table and then perform some data manipulation and
retrieval from the ##table.
Keith Kratochvil
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:egT2TU1eGHA.5088@.TK2MSFTNGP02.phx.gbl...
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:0E5B6748-F110-497E-ABE3-C9B90F6280FA@.microsoft.com...
>
> Also I have never found a valid use for an ##temp global temporary table.
> David
>|||Global temporary tables are cleaned up as follows (according to BOL):
"Global temporary tables are automatically dropped when the session
that created the table ends and all other tasks have stopped
referencing them. The association between a task and a table is
maintained only for the life of a single Transact-SQL statement. This
means that a global temporary table is dropped at the completion of the
last Transact-SQL statement that was actively referencing the table
when the creating session ended."
I didn't think that tempdb recycled to those rules, I thought it was
recreated upon startup only.
Cheers
Will|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ey10gf1eGHA.2416@.TK2MSFTNGP03.phx.gbl...
>I have used them a couple of times. It has been a while. From what I can
>remember we had to dynamically build several sql statements, insert them
>into one (##temporary) table and then perform some data manipulation and
>retrieval from the ##table.
>
But if you didn't retrieve them in another session, a #table would have
sufficed.
David|||"Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> wrote in message
news:%23Il8tY1eGHA.1208@.TK2MSFTNGP02.phx.gbl...
> You'll generally get better performance from in-memory temporary tables as
> long as they are fairly small. SQL Server will push the memory table to
> tempdb in certain situations such as row count or available memory. There
> are some limitations on indexing with in-memory temporary tables. BOL has
> a good section on temporary tables and explains better than I can without
> plagairism <g>.
> ## temporary tables are 'global' temporary tables and are cleaned up when
> sql server recycles tempdb. # temporary tables are local to the current
> batch and are removed when the batch has completed.
>
#table temp tables live for the life of the connection, not the batch.
There is an exeption for #temp tables created inside stored procedures.
They are automatically dropped after the stored procedure is invoked.
David|||As I mentioned, it has been a while. I don't remember all the gory details,
but the global temp table seemed like the best solution (or perhaps the only
solution) at the time.
Keith Kratochvil
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uGhk1E2eGHA.2188@.TK2MSFTNGP05.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:ey10gf1eGHA.2416@.TK2MSFTNGP03.phx.gbl...
> But if you didn't retrieve them in another session, a #table would have
> sufficed.
> David
>|||Has anyone ever used global temporary tables (##temp)? They seem awfully
useless.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uGhk1E2eGHA.2188@.TK2MSFTNGP05.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:ey10gf1eGHA.2416@.TK2MSFTNGP03.phx.gbl...
> But if you didn't retrieve them in another session, a #table would have
> sufficed.
> David
>