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

fnamelnamecitySalary
1Riyapatelvvnagar

30000

2

Nikunjpatelvvnagar10000

3

ChiragJoshianand

15000

4ShivaayShahnadiad

90000

5

AnikaPanchalbakrol

50000

6

AmishaPatelLondon

40000

  • delete row where the fname is Chirag

 delete from person where fname=’Chirag’;

 select * from person;

 output:

Id

fnamelnamecity

Salary

1Riyapatelvvnagar

30000

2

Nikunjpatelvvnagar10000
4ShivaayShahnadiad

90000

5

AnikaPanchalbakrol50000
6AmishaPatelLondon

40000

  • delete row where salary is less than 11000

 delete from person where salary<11000;

 select * from person;

 output:

Id

fnamelnamecitySalary
1Riyapatelvvnagar

30000

4

ShivaayShahnadiad90000
5AnikaPanchalbakrol

50000

6

AmishaPatelLondon

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

fnamelnamecitySalary
1Riyapatelvvnagar

30000

4

ShivaayShahnadiad90000

5

AnikaPanchalbakrol50000
6AmishaPatelLondon

40000

  • Truncate table

 Truncate table person;

Id

fnamelnamecity

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.

 

Leave a Reply

Top