Different Between Delete And Truncate in Sql Server

These two SQL commands are used to delete the data from the given table.
Please note that the table structure will not be deleted

Delete Command:

•  it is a DML commands.
•  you can use WHERE clause to filter the  data.
•  Delete operation is a Logged Operation  hence  we can rollback the operation
•  It is the slow operation to delete the rows
•  when we want to delete partial rows then we  can use DELETE command.
•  The Identity Column will never be Reset

 Syntax:
  DELETE FROM <TableName> WHERE <Condition>

Steps to create delete commands:
1. Create database with the name Murali

2. Use murali Database

3. Create table with the name muralidata

4. Insert some columns in the table

5. Select the table and see how many rows inserted with identity

After delete the Muralidata Table
insert a new row but the row started from the identity number 7

Truncate Command:

•  it is a DDL Commands in sql server.
•  we canot use WHERE clause
•  you can not use WHERE clause to filter   the   data.
•  It is non Logged operation hence we can not    rollback the operation.
•  it is faster to delete the rows.
•  when we want to delete all rows then we can    use TRUNCATE command.
•  The Identity Column will be Reset

 Syntax:
  TRUNCATE TABLE <TableName> 

Steps to truncate the table:

1. Create database with the name Murali

2. Use murali Database

3. Create table with the name muralidata

4. Insert some columns in the table

5. Select the table and see how many rows inserted with identity

6. Next truncate the table

7. Again the insert the column

8. Observe the Identity Number

9. The identity Number Started from Number 1

DROP Command:

 it is the DDL command
 it remove the structure and data

Syntax:
DROP table <Table Name>