Problem solve Get help with specific problems with your technologies, process and projects.

Group permission for truncating tables

Sybase expert Mich Talebzadeh explains how to authorize groups to truncate using a delete command with the temporary table.

I can truncate a table only if I'm the owner of the table. How can this ownership be changed to give a group permission to truncate a table?

The transact SQL command "truncate table" is a minimally-logged operation. As such, a truncate table is equivalent to but faster than a delete command without a where clause. Delete removes rows one at a time and logs each deleted row as a transaction; truncate table de-allocates whole data pages and makes fewer log entries. Both delete and truncate table reclaim the space occupied by the data and its associated indexes. Because the deleted rows are not logged individually, truncate table cannot fire a trigger. Since it is a non-recoverable command, then only the owner of the table can truncate the table. Normal users cannot truncate the table.

If you have an application which requires a table to be truncated after certain operations by the user than you, you may want to consider using a temporary table in tempdb (the so-called #table) for this purpose. If the table is a staging table in the database and has to be there all the time, then delete must be used. The problem with deleting all records is that it can cause the transaction log of database to fill up. For this reason, you may consider looping through records in a batch size, of say, 10,000 records, at a time and commit, afterwards, as the following example shows:

set rowcount 10000

while exists (select 1 from table_a)
begin tran
delete from table_a
if @@error != 0 or @@transtate = 3
print "ERROR : delete of table_a failed "
if @@trancount > 0 rollback tran
commit tran

Dig Deeper on Linux servers

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.