Ask the Expert

Group permission for truncating tables

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?

Requires Free Membership to View

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

This was first published in December 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: