Solved

where is my ##temp table

Posted on 2011-09-30
5
266 Views
Last Modified: 2012-06-21
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
Comment
Question by:rschmehl
  • 2
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 25 total points
ID: 36892670
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
 
LVL 2

Assisted Solution

by:akku101
akku101 earned 25 total points
ID: 36892716
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
 
LVL 2

Expert Comment

by:akku101
ID: 36892741
Sorry, global temp table is accessable for other sessions aswell.
0
 

Author Closing Comment

by:rschmehl
ID: 36892766
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36892770
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 44
Live Storage Vmotion VMs with shared VMDK 10 61
SQL Improvement  ( Speed) 14 28
Highest and 2nd Highest Totals 11 21
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question