Truncation is a data definition language (DDL) command used to delete all rows from a table. It removes the rows by unassigning the pages, creating an entry for de-mapping pages in the transaction log. It does not record every row deletion in the transaction log. Truncation is different from the DROP TABLE statement, which removes the entire table structure.
The TRUNCATE TABLE statement is used to remove complete data from an existing table, while maintaining the table structure for future use. The TRUNCATE () function returns n positions truncated to d decimal places. If you omit d, n is truncated to 0 decimal places. If d is a negative number, the function truncates the number n to d digits to the left of the decimal point. Oracle and PostgreSQL provide the TRUNC () function which has the same functionality as the TRUNCATE () function. Tables with more than 128 extensions can be deleted or truncated in Microsoft SQL Server without the need for concurrent locks on all extensions required for deletion.
We can see in the previous images that the DROP TABLE statement is faster than the TRUNCATE TABLE statement in SQL. In this example, the number of decimal places is, therefore, -2.The TRUNCATE () function truncates two digits to the left to decimal points. In SQL, the TRUNCATE TABLE statement is a data definition language (DDL) operation that marks the extent of a table for deallocation (empty for reuse). In SQL truncation, unlike drop table, which removes the entire table structure, it maintains the table structure for future use. TRUNCATE in Data Definition Language (DDL) is equivalent to DELETE in Data Manipulation Language (DML). The mytable TRUNCATE TABLE statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).
However, after the table is truncated, its structure, columns, indexes, constraints, relationships, views, etc. are preserved.
Leave Reply