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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×