In SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

, the TRUNCATE TABLE statement is a Data Definition Language

Data Definition Language

A data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...

 (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

Table (database)

In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...

, typically bypassing a number of integrity enforcing mechanisms. It was officially introduced in the SQL:2008 standard.

The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE

Delete (SQL)

In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...

 FROM mytable statement (without a WHERE

Where (SQL)

A WHERE clause in SQL specifies that a SQL Data Manipulation Language statement should only affect rows that meet specified criteria. The criteria are expressed in the form of predicates...

clause). The following characteristics distinguish TRUNCATE TABLE from DELETE:

• In the Oracle Database
  Oracle Database
  The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
  
  , TRUNCATE is implicitly preceded and followed by a commit
  Commit (data management)
  In the context of computer science and data management, commit refers to the idea of making a set of tentative changes permanent. A popular usage is at the end of a transaction. A commit is an act of committing.-Data management:...
  
   operation. (This may also be the case in MySQL
  MySQL
  MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
  
  , depending on the chosen storage engine.)
• Typically, TRUNCATE TABLE quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging
  Transaction log
  In the field of databases in computer science, a transaction log is a history of actions executed by a database management system to guarantee ACID properties over crashes or hardware failures...
  
   the deletions, as well as the number of locks
  Lock (database)
  A lock is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated when multiple users try to write to the database. Any single user can only modify those database records to which they have applied a lock that gives them exclusive...
  
   acquired. Records removed this way cannot be restored in a rollback operation. Two notable exceptions to this rule are the implementations found in PostgreSQL
  PostgreSQL
  PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
  
   and Microsoft SQL Server
  Microsoft SQL Server
  Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
  
  , both of which allow TRUNCATE TABLE statements to be committed or rolled back transactionally.
• You cannot specify a WHERE clause in a TRUNCATE TABLE statement—it is all or nothing.
• TRUNCATE TABLE cannot be used when a foreign key references the table to be truncated, since TRUNCATE TABLE statements do not fire triggers. This could result in inconsistent data because ON DELETE/ON UPDATE triggers would not fire.
• In some database systems, TRUNCATE TABLE resets the count of an Identity column
  Identity column
  An Identity column is a column in a database table that is made up of values generated by the database. This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle...
  
   back to the identity's seed.
• In Microsoft SQL Server
  Microsoft SQL Server
  Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
  
  2000 and beyond in full recovery mode, every change to the database is logged, so TRUNCATE TABLE statements can be used for tables involved in log shipping.

The source of this article is wikipedia, the free encyclopedia.  The text of this article is licensed under the GFDL.