Temporary table in SQL 7.0
I have three tables:
One global temp table created by "##", a local temp tables created by "#"
and a general table created in tempdb.
global : create table ##globaltable.
local : create table #localtable.
general : create table tempdb.dbo.temptable
I can see all of them in SQL Analyser by selecting sysobjects in tempdb, but
can not see the local and global temp table in EM.
1. Why ?
2. In SQL 6.5 I can specify tempdb in ram but not in 7.0. So how does
SQLServer handle them? Is the data in global temp table stored in memory?
then how about the local temp table and the general one in tempdb.
This question bother me for a long time, any feeback would be appreciated.
Re: Temporary table in SQL 7.0
> I can see all of them in SQL Analyser by selecting sysobjects in tempdb,
> can not see the local and global temp table in EM.
> 1. Why ?
You'll not be able to see the Local temporary table because it is scoped to
the connection that created it. The SEM creates it's own connection. I'm
going to guess that the SEM recognizes that the table is a global temp and
filters it. Use the QA to query tempdb..sysobjects. You see the global
temporary table there.
> 2. In SQL 6.5 I can specify tempdb in ram but not in 7.0. So how does
> SQLServer handle them? Is the data in global temp table stored in memory?
> then how about the local temp table and the general one in tempdb.
Microsoft removed the ability to place tempdb in RAM. The global temporary
table will be realized within tempdb as any user table would.
Michael Levy MCDBA, MCSD, MCT
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL