Delete command is used to delete all the rows from the table. If we use where clause then according to condition specific rows will be deleted. In delete command where clause indicate the rows that will deleted next.

Delete Table_Name where condition

                            

Table Name –It is the name of table which has to be updated

In the delete sql query where clause is optional it identifies the rows that will be deleted.

Now we take an example to understand this

CREATE TABLE [dbo].[E_Salary](
          [Id] [int] IDENTITY(1,1) NOT NULL,
          [S_Id] [varchar](50) NULL,
          [Name] [varchar](50) NULL,
          [Salary] [varchar](50) NULL
) ON [PRIMARY]                           
 
GO

And I have inserted few records

insert into test.dbo.E_Salary values(101,'amit',5000)
insert into test.dbo.E_Salary values(102,'arun',8000)
insert into test.dbo.E_Salary values(103,'ankur',9000)

 

output

To delete row where name is amit.

delete  from test.dbo.E_Salary
where name='amit'                   
To delete all rows
delete  from test.dbo.E_Salary 
 

Sql truncate statement

It is use to delete all rows from the table and it release memory containing the table.It is a DML command.

Syntax

truncate Table
table_name
to delete all rows from the table we this query
truncate Table test.dbo.E_salary

 

Difference between Delete command and truncate command

Delete command is use to delete rows given in the condition and if condition is not given then all rows will be deleted but it does not free space

After deleting record when we again insert record identity not delete

Truncate command also use to delete all rows from the table and it free space

After deleting record when we insert record its identity will also delete .

We cannot rollback in truncate

We can rollback in delete.

In truncate trigger not fired.

 Sql Drop Statement

It is use to remove table from the database. If we use drop statement all the rows of table is deleted along with table object. We cannot get it back when we use drop statement. We have to recreate table in the database. 

Syntax for drop a table

Drop table table_name

 

Query for removing table

drop table E_Salary

 

Drop is the DDL statement. DDL (Data Definition language) command have rollback but DML (Data manipulation language) command does not have rollback.

 You can also visit these related artciles and blogs 

What is the difference among "dropping a table", "truncating a table" and "deleting all records?"