#Table
-
what is # (hash) Table?
what is ##Table?
-
These are temporary tables. Single and Double hash defines the scope of these tables. Refer the link below where it tempory tables are very nicely explained.
http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
# is a Local temporary tables
## is a Global temporary tables
Local Temp table is valid only for that processor id(@@SPID) alone where as Global Temp table is valid across all the processor id (@@SPID), until it is dropped
Thanks!
-
yuvipoy (10/9/2012)
# is a Local temporary tables## is a Global temporary tables
Local Temp table is valid only for that processor id(@@SPID) alone where as Global Temp table is valid across all the processor id (@@SPID), until it is dropped
Thanks!
Not strictly true in regards to the ## temp tables, they are valid while the session that created them is active, once that session is closed they are automatically dropped.
Proof open new query window in SSMS
CREATE Table ##TempTable (Col1 Int)
GO
Insert into ##TempTable
Select 1
GO
Open second Query window
Select * from ##TempTable
should return 1 row
close SSMS Query window with the create statement and re-run the Select, you will get an "invalid object name" error as the ##temptable has been dropped.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Thanks
-
Jason-299789 (10/9/2012)
yuvipoy (10/9/2012)
# is a Local temporary tables## is a Global temporary tables
Local Temp table is valid only for that processor id(@@SPID) alone where as Global Temp table is valid across all the processor id (@@SPID), until it is dropped
Thanks!
Not strictly true in regards to the ## temp tables, they are valid while the session that created them is active, once that session is closed they are automatically dropped.
Proof open new query window in SSMS
CREATE Table ##TempTable (Col1 Int)
GO
Insert into ##TempTable
Select 1
GO
Open second Query window
Select * from ##TempTable
should return 1 row
close SSMS Query window with the create statement and re-run the Select, you will get an "invalid object name" error as the ##temptable has been dropped.
Not strictly true in regards to the ## temp tables!
They can still be "valid" after the session that created them is closed.
Here is relevant quote from BoL:
If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.
To try it out how it works, before executing SELECT in your second query window, open a transaction. You will see, that even after closing a session where you created ##table, you can still access it from the Query Window #2 or any other new query window you may want to open...
Actually, it's extremely important caveat. Not many people, for some reason, aware of this. And that may create real issues in systems which uses Global Temp tables without understanding of their behaviour (scope) completely.
-
Fair enough Eugine, I agree its an important caveat but does that mean unless you keep an open transaction on the table it will disappear once the transaction is closed if the creating session is also no longer active.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Jason-299789 (10/9/2012)
Fair enough Eugine, I agree its an important caveat but does that mean unless you keep an open transaction on the table it will disappear once the transaction is closed if the creating session is also no longer active.Yes it does, until, some other session will try to access it...
-
Eugene Elutin (10/9/2012)
Jason-299789 (10/9/2012)
Fair enough Eugine, I agree its an important caveat but does that mean unless you keep an open transaction on the table it will disappear once the transaction is closed if the creating session is also no longer active.Yes it does, until, some other session will try to access it...
Thanks for the response Eugine.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Just FMI:
has anyone of you ever used global temp tables?
I definitely didn't^^
-
Arthur Kirchner (10/9/2012)
Just FMI:has anyone of you ever used global temp tables?
I definitely didn't^^
I did.
Great tool for some one-off & ad hoc tasks.
But also, can be used in more advanced processing where sharing of temporary data is required, but creating permanent work tables is not appropriate for one or another reason.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply