You are here

Delete Statement

DELETE and TRUNCATE TABLE

DELETE STATEMENT

In this post we will discuss about the delete and truncate statement in sql server 2016.In SQL we can delete rows from the table using DELETE and TRUNCATE TABLE.

DELETE:

  • With the use of Delete statement we can delete rows from the table. we can also specify the predicate to restrict the rows to be deleted.

Syntax:

Delete from <table_name> where <predicate>;

  • If we do not specify a predicate, all rows from the target table will be deleted.

Example:

Table : person

Id

fname lname city Salary
1 Riya patel vvnagar

30000

2

Nikunj patel vvnagar 10000

3

Chirag Joshi anand

15000

4 Shivaay Shah nadiad

90000

5

Anika Panchal bakrol

50000

6

Amisha Patel London

40000

  • delete row where the fname is Chirag

          delete from person where fname=’Chirag’;

          select * from person;

        output:

Id

fname lname city

Salary

1 Riya patel vvnagar

30000

2

Nikunj patel vvnagar 10000
4 Shivaay Shah nadiad

90000

5

Anika Panchal bakrol 50000
6 Amisha Patel London

40000

  • delete row where salary is less than 11000

          delete from person where salary<11000;

         select * from person;

         output:

Id

fname lname city Salary
1 Riya patel vvnagar

30000

4

Shivaay Shah nadiad 90000
5 Anika Panchal bakrol

50000

6

Amisha Patel London

40000

 TRUNCATE:

  • In SQL truncate table is optimized statement that delete all rows from the target table or partition.
  • The truncate table does not support where clause.

Syntax:

Truncate table <table_name>;

Example:

Table :person

Id

fname lname city Salary
1 Riya patel vvnagar

30000

4

Shivaay Shah nadiad 90000

5

Anika Panchal bakrol 50000
6 Amisha Patel London

40000

  • Truncate table

         Truncate table person;

Id

fname lname city

Salary

Difference between Delete and Truncate:

Delete

Truncate

Sql server record in the log for deleted data. Sql server record which pages were deallocated.
It is DML statement. It is DDL statement .
It is slower. It is faster.
Delete is allowed if a foreign key is pointing to the table. Truncate is not allowed if a foreign key is pointing to the table.
Rollback is not allowed. Rollback is allowed.
It requires a delete permission. It requires a alter permission on the table.
It allows where clause. It does not allows where clause.
Delete tables can have trigger. Truncate tables can not have trigger.
It is allowed against a table involved in an indexed view. Truncate is disallowed in such a case.

Keep Visiting TechwithR.com for more upcoming top technical articles.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

 

One thought on “Delete Statement

  1. Very nice discussion about delete statement. I search for truncate and delete statement difference and I finaly find it here in your blog. Thank you so much…

Leave a Reply

Top