The SQL truncate TABLE command is used to delete all data from an existing table. It is a data definition language (DDL) command that labels the extensions of a table for de-mapping (emptying for reuse). Unlike the DROP TABLE command, which removes the entire table structure, it keeps the table structure for future use. The TRUNCATE TABLE statement removes data from a table, but not the table itself.
It performs the same function as a DELETE statement without a WHERE clause and deletes all rows from a table by unallocating pages that are used to store table data. The SQL TRUNCATE TABLE statement is used to delete all records in a table. It makes an entry for page deallocation in the transaction log, but does not record deletion of each row in the transaction log. The TRUNCATE () function returns n with d truncated decimal places.
It truncates from n to 0 decimal places if you omit d and truncates the number from n to d digits on the left to the decimal point if d is a negative number. To delete all records from a table, you can include the select query before and after the truncation operation. In this way, the RDBMS can reduce the resources for logging and the number of locks that need to be acquired. However, after the table is truncated, its structure, columns, indexes, constraints, relations, views, etc. are preserved. The table owner, members of the sysadmin fixed server role, database owner, and DBDDL administrator fixed database roles have TRUNCATE TABLE permissions by default and are not transferable.
Once you truncate a table, you cannot use a flashback table statement to retrieve the contents of the table. In summary, SQL Truncate is used to delete all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause and is faster than the DROP TABLE statement in SQL. Logically, it has the same effect as deleting all data from a table. To delete all records from a large table quickly, you can use the TRUNCATE TABLE statement.
Leave Reply