SQL Server Performance Series – Unfiltered Delete

By Navratan Verma

It was interesting thing observed at one of the client where the developer was using unfiltered delete on a staging table which was further used down in the stored procedure.

Delete from TempLoanPayment

When there is a need to delete records from staging table without any condition it is better to use truncate rather than using delete, as delete requires to update any underlying index and also a fully logged operation.

DeleteNonFil

It is recommended to use Truncate table TempLoanPayment

Note: Truncate table should not used on table having referred into foreign key constraint and truncate resets the identity column.

Thanks

 

Leave a Reply

Your email address will not be published. Required fields are marked *