You are currently viewing How To Delete Data From Table in SQL Server

How To Delete Data From Table in SQL Server

  • 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