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]
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)
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.
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