Truncate in DBMS

January 27, 2023

TRUNCATE TABLE

On this article, we will learn about TRUNCATE in DBMS . It is one of the data definition language (DDL) commands.We will discuss about its syntax and how it is different from DELETE Command.

Drop in DBMS

TRUNCATE IN DBMS

Sometimes the user wants to permanently delete the existing table or wants to delete the existing data alone or change the table name all these purposes are served using 3 DDL(data definition language) commands drop, truncate and rename respectively

TRUNCATE command

  • The truncate command will make the table empty i.e all the table data will be deleted but the structure and database object is still alive and the table can be reused normally
  • The truncate command logically nothing but using delete command for deleting the records in the table without specifying the where condition i.e all the rows get deleted in that case
DBMS desktop gif
DBMS mobile adv gif

Functional Dependencies

Joins in DBMS

Relational Algebra

Deadlock in DBMS

Syntax 

TRUNCATE TABLE table_name;

Example 

truncate table emp;
O/P 
Table emp truncated successfully
After truncating the table if you try to display the table data using a select statement then you’ll get a message as” 0 rows selected” i.e nothing to display because the table is empty without any rows

How TRUNCATE Is Different From DELETE?

DELETE TRUNCATE
The DELETE command is used to delete specified rows(one or more). While this command is used to delete all the rows from a table
DML COMMAND DDL COMMAND(Autocommit)
Can use where condition with delete st Can use where condition with truncate st
Delete specific rows”Where” condition All rows will be removed from the table
Slower,because it used undo segment Faster
“Delete”triggers will get fired No triggers will get invoked
Won’t reclaim the space used by table Reclaim the space used by table
Won’t reset the high level watermark Reset the high level watermark

Syntax: DELETE command

DELETE FROM TableName 
WHERE condition;

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get Prime
DBMS Tutorial banner