SQL Server How to Delete Records into Table

In the previous post you have learnt how to insert rows into table in SQL Server database. When table is populated with rows, database size increases. Over the course of time it becomes necessary to delete unwanted records after backing up the database. In this tutorial you will learn how to deleted unwanted records from a table in SQL Server.

How to Delete Records in Table

DELETE FROM command is used to remove one or more record from a table. The FROM keyword in the DELETE command is optional. If you want to delete only some records of a table then WHERE clause should be used to filter the records; the filtered records will be deleted as per the WHERE condition.

 DELETE FROM Employees

 WHERE Gender = 'F'

Using a Lookup Table to Delete Records

Suppose you want to delete records from a Table based on some records of the lookup table in the cases you can create a sub query using lookup table then and there after you can use this sub query with the delete command.

To delete some records from a Table based on the result set returned by the join operation, You must use FROM clause before the result set and just after the table name which records is being deleted.

TRUNCATE TABLE command can be used only by the table owner but DELETE command can be used to delete a table by any person who has been given the permission of the table.

TRUNCATE TABLE Employees

When you run DELETE command then transaction log is generated but in case of TRUNCATE TABLE command log is not generated.

Delete Trigger is not fired when table is truncated but delete Trigger is fired when data is deleted using delete command.

The TRUNCATE TABLE command resets the Identity value but delete command doesn't.

In the next post, you will learn how to update rows of table in SQL Server in different ways.