Truncate Table|Truncate Table Statement in SQL - sql - sql tutorial - learn sql



  • Sometimes we wish to get rid of all the data in a table.
  • One way of doing this is with DROP TABLE.
  • But what if we wish to simply delete the data but not the table itself? For this, we can use the TRUNCATE TABLE command.
  • In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse).
  • The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms.
  • It was officially introduced in the SQL:2008 standard.
  • The syntax for TRUNCATE TABLE is,
TRUNCATE TABLE "table_name";
  • So, if we wanted to truncate the Customer table that we created in SQL CREATE TABLE, we simply type,
TRUNCATE TABLE Wikitechy;
  • Please note that the TRUNCATE TABLE command cannot delete any rows of data that would violate FOREIGN KEY or other constraints.

Truncate Table vs Delete

Delete vs Truncate
  • Functionally, the following two SQL statements are equivalent. Both will delete all rows from the Wikitechy table:
TRUNCATE TABLE Wikitechy;
  • and
DELETE FROM Wikitechy;
  • The difference between the two is in the amount of system resources consumed.
  • DELETE FROM requires more system resources, and hence takes longer to complete, because the RDBMS has to record all changes one row at a time in the transaction log, while a TRUNCATE TABLE operation does not record the change one row at a time, so it can be completed quicker.

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Truncate Table |Truncate Table Statement in SQL