Re: Truncate Table vs. Delete From

From: VUILLERMET Jacques (jvuillermet@opera-sas.fr)
Date: 08/26/02 

From: "VUILLERMET Jacques" <jvuillermet@opera-sas.fr>
Date: Mon, 26 Aug 2002 14:49:41 +0200

I think that TRUNCATE is a non-logged operation (no rollback possible), then
faster.

Jacques.

"Don Reinhard" <reinhard@stjosephs-marshfield.org> a écrit dans le message
de news: c5f201c2494a$40b18ae0$39ef2ecf@TKMSFTNGXA08...
> In the books on-line, I found the following excerpt on
> this subject:
>
> If you want to delete all the rows in a table, TRUNCATE
> TABLE is faster than DELETE. DELETE physically removes
> rows one at a time and records each deleted row in the
> transaction log. TRUNCATE TABLE deallocates all pages
> associated with the table. For this reason, TRUNCATE TABLE
> is faster and requires less transaction log space than
> DELETE. TRUNCATE TABLE is functionally equivalent to
> DELETE with no WHERE clause, but TRUNCATE TABLE cannot be
> used with tables referenced by foreign keys. Both DELETE
> and TRUNCATE TABLE make the space occupied by the deleted
> rows available for the storage of new data.
>
> Permissions:
> Delete permissions default to members of the sysadmin
> fixed server role, the db_owner and db_datawriter fixed
> database roles, and the table owner. Members of the
> sysadmin, db_owner, and the db_securityadmin roles, and
> the table owner can transfer permissions to other users.
> SELECT permissions are also required if the statement
> contains a WHERE clause.
>
> With a recent experience, there is more to this than meets
> the eye. Using the premise that having execute
> permissions on a Stored Procedure overrides any
> permissions set on a table used within a SP, I did some
> more security modeling/testing.
>
> Here's what I did. I used TestAcct as the guinea pig
> account. I setup a user-defined role. TestAcct was
> assigned to that role. The role had execute permissions
> on a Stored Procedure.
>
> TestAcct executed the Stored Procedure which issued a
> Truncate Table command. The procedure failed because of
> lack of permissions to perform the command on the table.
>
> I changed the Truncate Table command to Delete From and it
> worked. The table involved does not have any foreign keys.
>
> I was wondering if anyone knows the explanation for this
> apparent difference between Truncate Table and Delete From.
> Thanks,
> Don
>

Relevant Pages

  • Re: Stored procedures + table permissions
    ... "TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable." ... I have a stored procedure 'DoTurnover' which should do a 'truncate table' on two specific tables; how can I achieve to give this procedure the proper permission? ...
    (microsoft.public.sqlserver.programming)
  • Re: Truncate Table vs. Delete From
    ... TRUNCATE TABLE permissions default to the table owner, ... of the sysadmin fixed server role, ...
    (microsoft.public.sqlserver.security)
  • RE: truncate table
    ... this gives you the option to create a stored procedure with a truncate table statement. ... You can then GRANT the stored procedure to be EXECUTED AS a login or role. ... >>Truncate table permissions are NOT transferable. ...
    (microsoft.public.sqlserver.server)
  • Permissions for executing a stored procedure
    ... Truncate table permissions only default to table owner, ... So, eventhough, you have granted execute permissions to ... Instead using TRUNCATE TABLE, you could use DELETE FROM, ... >We have a stored procedure that has code in it to ...
    (microsoft.public.sqlserver.security)
  • Re: With in a SP Truncate dbo.table table-name permissions..
    ... TRUNCATE TABLE permissions default to the table owner, ... According to sql server ... If a user who creates a stored procedure does ...
    (microsoft.public.sqlserver.security)