- Delete statement is used to delete some(using where clause) or all records from the table.
- Delete statement is a DML (Data Manipulation Language) command.
- It locks each and every row before deletion.
- It creates log for deleted records.
- Deleted records can be rolled back using rollback transaction.
- Most important part is that it does not reset identity column after deleting records.
- Delete statement runs slower because it takes time in locking each row and creating log for every deleted row.
DELETE from Table
For this demo, we create a table tblEmployee and insert some records.
CREATE TABLE tblEmployee ( EmpID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, EmpFirstName NVARCHAR(20) NULL, EmpLastName NVARCHAR(20) NULL, EmpAge INT NULL, EmpEmail NVARCHAR(50) NULL, EmpPhone NVARCHAR(20) NULL, EmpSalary INT NULL, EmpJoiningDate DATETIME NULL ) GO
INSERT INTO tblEmployee (EmpFirstName,EmpLastName,EmpAge,EmpEmail,EmpPhone,EmpSalary,EmpJoiningDate) VALUES ('John','Dave',26,'firstname.lastname@example.org','091111253521',26000,'2010-01-01'), ('Kamran','Akmal',35,'email@example.com','092113693521',25000,'2011-01-01'), ('Umar','Akmal',36,'firstname.lastname@example.org','092111226521',38000,'2015-01-01'), ('Hafeez','Shaikh',25,'email@example.com','0921141252103',25000,'2016-05-01'), ('Amar','Sidhu',36,'firstname.lastname@example.org','093222226521',50000,'2020-01-01') GO
We have the following entries when we select data from tblEmployee.
DELETE a single row from table
Now we delete a single row from the table using a where clause. Let’s delete a record with EmpID 2.
DELETE FROM tblEmployee WHERE EmpID=2
This will delete the record whose EmpID is 2. Now we have only four records as shown below.
DELETE all rows from a table
To delete all rows from the table, we use the following query.
DELETE FROM tblEmployee
After deleting all rows from a table, we have nothing in tblEmployee as shown below.