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.

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

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