Temporary tables

Question

Temporary tables

sdjuricic

Grasshopper

Points: 15
More actions
November 2, 2007 at 4:20 pm

#179782

Hello,
I have a java application that is running on Oracle and we would like to port it to sql server 2000.
In oracle we are using global temporary tables with an option to delete all rows on commit. I saw that sql server has a similar concept of local temporary tables but it doesn't seem to have an option to delete on commit. The local temporary table is visible per connection but since we do not close connection, a connection can be associated with more than a single java session. The local temporary table in sql server gets dropped when the connection is closed.
What is the best way for me to mimic Oracle's global temporary tables with delete on commit;
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

SQL Server also has Global Temp tables (##) which are a bit different than regular (#) temp tables.

The real key, though, is that you may be doing something wrong in the code if you need to use Global Temp tables... what is it that you're doing to need them?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sdjuricic Grasshopper Points: 15 More actions · Answer 2

Thanks for your reply,

Reason why we are using temporary tables in oracle is that we have a query engine that besides doing sql queries on a relational database it also does a spatial query via a third party utility ( not sql query ). So in a single query we are mixing sql with just java API. The temporary results from the spatial query we store in a temporary table and then we do a join with just regular tables.

I oracle we just used global temporary tables which has two features we need. All the data is erased after the transaction commits. During the transaction the data in the table from one connection is not visible to the other connection.

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 3

Modify your script to drop the table explicitly at the end otherwise your tempdb will choke if you have multiple users creating objects in tempdb and they are not dropped immediately. SQL Server does a good job of identifying the objects not in use and dropping them but can crawl sometimes whent he usage is heavy. It will be more efficient if you drop your tables yourself.."clean up your mess yourself" 😉

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

sdjuricic (11/5/2007)
Thanks for your reply,
Reason why we are using temporary tables in oracle is that we have a query engine that besides doing sql queries on a relational database it also does a spatial query via a third party utility ( not sql query ). So in a single query we are mixing sql with just java API. The temporary results from the spatial query we store in a temporary table and then we do a join with just regular tables.
I oracle we just used global temporary tables which has two features we need. All the data is erased after the transaction commits. During the transaction the data in the table from one connection is not visible to the other connection.

Then, I believe what you want is the normal (#) temp table in SQL Server...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60476 More actions · Answer 5

I agree with Jeff. If you want to empty your temporary table at the end of the transaction, use a TRUNCATE statement before the final COMMIT.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

sdjuricic Grasshopper Points: 15 More actions · Answer 6

sdjuricic

Grasshopper

Points: 15

November 7, 2007 at 1:09 pm

#749204

Thank you for your suggestions I will try it out.