Recent entries
 
Nov 17

Written by: Yuri Rusakov
11/17/2008 1:49 PM

 

Author: Yuri Rusakov,  DB Best Technologies
 
One important point still is missing in our solution. Namely, we should modify all DML statements that work with emulated temporary table. It is necessary because we need to use s_id and s_lt columns to provide session isolation. You can see below how the source statements should look after the conversion.
 
 
                  INSERT ... VALUES statement
Oracle:
INSERT INTO VALUES ()
 
SQL Server:
INSERT INTO SELECT @@SPID, login_time, FROM sys.dm_exec_sessions WHERE session_id=@@SPID
 
 
                 INSERT ... SELECT statement
Oracle:
INSERT INTO SELECT FROM
 
SQL Server:
INSERT INTO SELECT @@SPID, s.login_time, FROM sys.dm_exec_sessions s cross join WHERE s.session_id=@@SPID
 
 
 
                 UPDATE statement
 
Oracle:
UPDATE SET [WHERE ]
 
SQL Server:
UPDATE SET WHERE s_id=@@SPID and s_lt=(SELECT login_time FROM sys.dm_exec_sessions WHERE session_id=@@SPID) [and ()]
 
 
                  DELETE statement
 
Oracle:
DELETE FROM [WHERE ]
 
SQL Server:
DELETE FROM WHERE s_id=@@SPID and s_lt=(SELECT login_time FROM sys.dm_exec_sessions WHERE session_id=@@SPID) [and ()]
 
 
                  SELECT statement
Oracle:
SELECT FROM [WHERE ]
 
SQL Server:
SELECT FROM WHERE s_id=@@SPID and s_lt=(SELECT login_time FROM sys.dm_exec_sessions WHERE session_id=@@SPID) [and ()]
 
 
If the source GTT is transaction-specific, that is, the definition contains ON COMMIT DELETE ROWS clause, then every COMMIT statement should be preceded with the following code.
 
SQL Server:
if @@TRANCOUNT=1 
      DELETE FROM WHERE s_id=@@SPID and s_lt=(SELECT login_time FROM sys.dm_exec_sessions WHERE session_id=@@SPID)
 
 

 

 

Tags:

156 comments so far...

Re: How to Emulate Oracle Global Temporary Tables in SQL Server Database (part 3)

The computer language SQL which is designed for managing data helps us to access databases. Global temporary tables are used when the data that has to be processed from SQL is too large. In this situation the normal tables will not be sufficient. It is found that the use of global temporary tables can improve the performance of SQL. With the use of global temporary tables in SQL the user can easily access databases. These global temporary tables can increase the speed of queries which do summariz