Prev: Single Row from Multiple Columns and Rows
Next: GUID In Table
From: tshad on 14 Apr 2010 19:01
I need to delete all the rows from my temp table and use it again in my
stored procedure.

I don't want to delete all the records (slower with logging and locking
issues) and am thinking of either Truncating or dropping and recreating the
table.

I know you have to be dbo to truncate the table. I am running this from a
stored procedure that anyone can call. Would this be an issue.

I also don't want to do another DDL statements (Drop and Create) in the
middle of my code. I had heard that if you do DDL statements in the middle
of the code it causes recompiles where it doesn't if it is at the start of
the procedure (could be wrong here).

I know you can't truncate a table with foreign keys - are indexes an issue?

Thanks,

Tom


From: Eric Isaacs on 14 Apr 2010 19:26
First, if the table is a true temp table in TempDB, those transactions
are not logged. If you use a #Tablename table, a table for each
process id is created in TempDB. You would be better off dropping the
table and recreating it than deleting all the rows (as far as speed
goes, and assuming you're dealing with a lot of rows.) Another option
to consider is just creating a different #Tablename table with the
same structure and using it instead and at the beginning of your
stored procedure.

If you create your procedure WITH EXECUTE AS OWNER and the owner is
DBO, you might be able to truncate the table as any user. You would
need to test that.

If you're using foreign keys in your #temp tables, they probably
shouldn't be #temp tables. Indexing temp tables makes sense,
establishing foreign keys is probably overkill and will add to the
overhead of loading data into the temp tables.

-Eric Isaacs
From: Plamen Ratchev on 14 Apr 2010 22:07
Each user that runs the stored procedure will have a separate copy of the temp table, so not an issue to truncate. Also,
indexes are not a problem when truncating.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Single Row from Multiple Columns and Rows
Next: GUID In Table