Q

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
begin tran
delete from table_a
if @@error != 0 or @@transtate = 3
begin
print "ERROR : delete of table_a failed "
if @@trancount > 0 rollback tran
return
end
commit tran
end

This was first published in December 2005

Dig deeper on Open source databases

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close