Global Temp Tables - SQL Server vs Oracle

Question

I am using Oracle 11g Global Temporary Tables as I need a solution where I can add rows to a temporary table for a join, and I want only the rows added to the temp table for the Oracle Connection/session to be included. I am using Global Temp Table in Oracle becuase I want the table to exist between sessions so it doesn't have to be recreated every time I create a query. This is working out fine.

My Oracle table definition is as follows:

CREATE GLOBAL TEMPORARY TABLE book_id_temp 
( 
   book_id RAW(32)
)ON COMMIT DELETE ROWS;

I have the same database structure also on the SQL Server 2008-R2 side, and need a similar solution in SQL Server. I want to :

1. Open a SQL Connection (ADO.NET)
2. Within a Transaction:
3. -Add rows to a temp table.
4. -Join them on another table, SELECT the results
5. -Have only the rows added during this session be included in the join. Another thread may be executing on the same temporary table. It's possible then that a local temp table would be best here?
6. Rollback the entire transaction.

From what I've read of global temporary tables in SQL Server, the tables exist after a connection is ended, like a regular table, and like a Global Temp Table in Oracle. However, it's not clear on the scope of the data. Does only the SQL Server session that created the rows have access to it, like in Oracle? What is the accessability of the data with SQL Server Global Temp Tables? Do you have a suggestion of an alternative to achieve my goal?

Answer 1

Temporary tables in Oracle are permanent objects that hold temporary data that is session local. Temporary tables in SQL Server are temporary objects.

1. In SQL Server, a global temp table holds data that is visible to all sessions. "Global temporary tables are visible to any user and any connection after they are created." http://msdn.microsoft.com/en-us/library/ms186986.aspx
2. Global temp tables are still temporary objects that do not persist indefinitely, and may need to be created before use. "Global temporary tables are ... are deleted when all users that are referencing the table disconnect from the instance of SQL Server." http://msdn.microsoft.com/en-us/library/ms186986.aspx

I find that a local temporary table, or table variable, is the closest to being the same to Oracle's global temp table, the big difference is you have to create it every time.

Usually, in a case like yours, step 3, add rows to temp table, would be done by doing a select ... into #temp_table_name .... (equivalent to Oracle create table ... as select ...) http://msdn.microsoft.com/en-us/library/ms188029.aspx

Also, you can't do the following in a stored proc: (pseudo code.)

begin proc
   call another proc to create local temp table.
   use temp table
end proc

Local temp tables are destroyed when returning from the stored procedure that created them.

Update 2014-10-14: The behavior of local temp tables is different in the Parallel Data Warehousev version of SQL Server. Temporary tables are not dropped on exit from the stored procedure that created them, and instead continue existing for the rest of the session. This behavior observed on:

select @@version
Microsoft SQL Server 2012 - 10.0.5108.1 (X64) Jun 24 2014 20:17:02 Copyright (c) Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Answer 2

Temporary tables on SQL Server are local by default. The table will be dropped after the session ends. If you execute a script like:

create table #Foo (
       FooID  int
      ,FooCode1  varchar (20)
)

insert table #Foo (FooID, FooCode1)
values (1001, 'X')

insert table #Foo (FooID, FooCode1)
values (1002, 'Y')

select f.FooID
      ,f.FooCode1
      ,b.BarID
      ,b.BarCode1
  from #foo f
  join bar b
    on bar.FooID = f.FooID -- (or whatever predicate)

The query will only return rows that join on what you inserted into #Foo in this session. #Foo is local to the session; you can have multiple sessions with their own #Foo temporary table with no worries about namespace collisions. When the session is closed the temporary table will be discarded. You can also explicitly drop #Foo after you've finished with it if you're using a persistent database connection (e.g. a client-server desktop app).