Group permission for truncating tables

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

    Login

    When you register, my team of editors will also send you resources covering Linux administration and management; integration and interoperability between Linux, Windows and Unix; securing Linux and mixed-platform environments; and migrating to Linux.

    Margie Semilof, Editorial Director

    By submitting your registration information to SearchEnterpriseLinux.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchEnterpriseLinux.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Back to top