What is Truncating in SSIS and Why is it Better Than Deleting?

Truncation is a process that occurs when a string of one length is wrapped in a field that contains a string of shorter length. In most cases, the last characters of the string are cut off to fit in the field. This can happen when converting values from one data type to another. Using truncation is better than using delete, as it ignores all indexes and simply deletes everything. The Execute SQL Task in the control flow can be used to perform truncation.

This task should be added before the table insert operation is performed. The code for this task should include the Truncate and Load pattern, which deletes all rows from the destination table and then reloads it from the source completely. Truncation requires “ALTER DATABASE” permissions, while DELETE is its own permission and can be granted on individual tables. It is faster than DELETE and uses fewer system log and transaction resources.

Microsoft SQL Server has the ability to delete or truncate tables that have more than 128 extensions without maintaining simultaneous locks on all extensions required for deletion. TRUNCATE TABLE removes all rows from a table, but the structure of the table and its columns, constraints, indexes, etc. remain. To truncate a partitioned table, the table and indexes must be aligned (partitioned in the same partitioning function). TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table data and records only page deallocations in the transaction log.The WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.

However, you can incorporate the TRUNCATE TABLE statement into a module, such as a stored procedure, and grant the appropriate permissions to the module using the EXECUTE AS clause. The expression evaluator identifies explicit conversions that can cause truncation and issues a warning when the expression is parsed. TRUNCATE TABLE permissions are default for the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. In conclusion, truncating tables is better than deleting them as it is faster and uses fewer system log and transaction resources. It also requires fewer permissions than deleting tables.

Furthermore, it does not affect any of the structure of the table or its columns, constraints, indexes etc., while deleting does.