- 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,'john@gmail.com','091111253521',26000,'2010-01-01'), ('Kamran','Akmal',35,'kamran@gmail.com','092113693521',25000,'2011-01-01'), ('Umar','Akmal',36,'umar@gmail.com','092111226521',38000,'2015-01-01'), ('Hafeez','Shaikh',25,'hafeez@gmail.com','0921141252103',25000,'2016-05-01'), ('Amar','Sidhu',36,'amar@gmail.com','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.

Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL