Quick SQL question. Is this a correct assumption about temp tables?

Factual Questions
1

Hello SQL doper experts.

A while ago I created php page for an intranet that used a complex query.

In order to speed up the query I replaced a subquery with a table created using ‘select into’ so that I could then do a ‘select * from’ on that table in the next query. This would speed things up as it would only have to be run once (subqueries seemed to be run for each conditional test thus slowing down the overall query massively)

To simplify I did this…




select somevariable into temptable 
from somebigtable where somevariable < somecondition

select somevariable,anothervariable,yetanothervariable 
from somebigtable where somevariable not in 
(select * from temptable)

This would work fine, and be quick. But it created a potential headache - if more than one person happened to be running this report at the same time there would be an access conflict on ‘temptable’.

I have just googled temporary tables. I was not able to get my answer to this specific question but I did find out how to create a temporary table. So my question is this - will the following query solve the conflict problem…?




select somevariable into **#**temptable 
from somebigtable where somevariable < somecondition

select somevariable,anothervariable,yetanothervariable 
from somebigtable where somevariable not in 
(select * from **#**temptable)

In other words - in this context, does adding the hash cause this table to only ‘exist’ for this scope? and thus avoid conflicts?

2

If this is MS SQL Server then the answer is “yes”, that will solve your problem

The actual table name in the temp database is a big long string of chars that is unique

3

Temp tables in SQL Server come in two varieties: session and global.

#SessionTempTable will only be available to your session; even if another person creates a #SessionTempTable in their session, theirs will be different.

##GlobalTempTable will be available to everyone.

More info: Temporary Tables - SQLTeam.com

4

If you can’t get a session/batch level temporary table, you can have a global table and add an additional column, let’s call it Session.

When you start your processing, create a new unique GUID and store it in a local variable, and insert that too, something like (sorry, I am a SQL Server guy and this may be a mixed dialect answer)

DECLARE @Session UniqueIdentifier
SELECT @Session = NewId()
select somevariable, @Session into temptable
from somebigtable where somevariable < somecondition

select somevariable,anothervariable,yetanothervariable
from somebigtable where somevariable not in
(select * from temptable WHERE Session=@Session)

DELETE FROM
temptable
WHERE
Session=@Session

5

It depends upon your flavor of SQL. Oracle has “global temporary” tables. The table is globally accessible, but the data is private per session.