Should I commit or rollback a transaction that creates a temp table, reads, then deletes it?

If we are talking about local temporary table (i.e. The name is prefixed with a single ), the moment you close your connection, SQL Server will kill the table. Thus, assuming your data layer is well designed to keep connections open as short a time as possible, I would not worry about wrapping the creation of temp tables in a transaction.

If we are talking about local temporary table (i.e. The name is prefixed with a single #), the moment you close your connection, SQL Server will kill the table. Thus, assuming your data layer is well designed to keep connections open as short a time as possible, I would not worry about wrapping the creation of temp tables in a transaction.

I suppose there could be a slight performance difference of wrapping the table in a transaction but I would bet it is so small as to be inconsequential compared to the cost of keeping a transaction open longer due to the time to create and populate the temp table.

I'm not really asking about the performance of wrapping it in a transaction. The connection is passed to multiple database API functions in succession, and they use a common temp table name/convention, so the transaction guaranteeing the deletion of the temp table is necessary. What I'm interested in is the effect of committing that transaction or rolling it back.

– Triynko May 21 '10 at 2:11 @Triynko - In what respect are you curious about the effect? Accessibility to the temp table from other code? – Thomas May 21 '10 at 4:20 @Thomas - I'm asking about the performance of commiting vs rolling back the transaction.It's really not intuitive, because rolling back seems to imply that work would be done, but if such a simple uncommitted transaction is rolled back... the work may just be dropped from memory and no work to "apply" the changes would be recorded, like updating database stats.

It's hard to say, and so I'm really looking for someone who knows what may go on in SQL Server under the hood during rollbacks and commits in this particular scenario. – Triynko May 21 '10 at 16:08 @Triynko - I think I understand the confusion. Even if something is in a transaction, the "work" is still done.

Even if you create a temp table in a transaction, populate it, do a bunch of work and then rollback, the work of putting the table into tempdb and writing data to disc, as if it were not in a transaction, is still done. The only difference is that this work is not visible to code outside the transaction using normal transaction isolation levels. – Thomas May 21 '10 at 16:21 @Triynko - Keep in mind that if your entire operation is not in a transaction, each DDL or DML statement is itself a transaction.

So, now the question is whether a large commit/rollback is cheaper than lots of smaller commits/rollbacks. In the long run, it is not cheaper IMO because of the additional blocking that you might be creating by having a long running transaction. – Thomas May 21 '10 at 16:23.

A simpler way to insure that the temp table is deleted is to create it using the # sign. CREATE TABLE #mytable ( rowID int, rowName char(30) ) The # tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server.

When the session is closed, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

The others are covered in Books Online. Temporary tables are created in tempdb. If you do this, you won't have to wrap it in a transaction.

I said I'm already using #temp tables. But the connection is reused as it's passed to multiple database API functions in rapid succession, so it's important that there's a transaction, which explicitly deletes the table when finished, either by following through with the delete and commit, or by rolling back the transaction. I'm specifically interested in the effects of either committing or rolling back the transaction (assuming the table is definitely deleted before the commit and possibly deleted before the rollback).

– Triynko May 21 '10 at 2:17.

A simpler way to insure that the temp table is deleted is to create it using the # sign.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.