Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

where is my ##temp table

I have a proc which creats ##tempb but does not drop it until the next execution. my question is does SSMS keep that table in the  TABLE view or someplace else. Even after a refresh i dot see it in the list view but I knows it's still there because i can select off it
0
rschmehl
Asked:
rschmehl
  • 2
  • 2
2 Solutions
 
Lee SavidgeCommented:
The table you have is a global temporary table and is stored in tempdb.

For a session based temporary table, use a single # rather than ##
0
 
akku101Commented:
This gloabal temp table is stored in TempDB and will be accessable from the session created, It will be better if you Include drop statement at the end of the SP to avoid Tempdb to grow unnecessarly.
0
 
akku101Commented:
Sorry, global temp table is accessable for other sessions aswell.
0
 
rschmehlAuthor Commented:
I found it.  I was looking in the DB table view and should have been looking at the view which has the master etc  tempdb    thanks
for the help
0
 
Lee SavidgeCommented:
That's correct. A global table is available to all sessions. A session table (#tempb) is only available for that session.

For good housekeeping in code if you have finished with a temporary table, drop it. Don't leave it for SQL to deal with. Bad practice. Also, consider using table variables rather than temporary tables wher you can. The performance benefit is worth it. They can do almost everything that a temporary and real table can do. They aren't written to disk and are destroyed at the end of the session from memory.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now