How to Delete Records from a Table in SQL Server

Here are two approaches to delete records from a table in SQL Server:

(1) Delete records based on specified conditions:

DELETE FROM [database_name].[table_schema].[table_name]
WHERE condition

(2) Delete all the records in a given table:

DELETE FROM [database_name].[table_schema].[table_name]

In the next section, you’ll see how to apply the above approaches using a practical example.

The Example:

Suppose that you created a table in SQL Server, where:

  • The table name (with the schema) is: dbo.Person
  • The table has 3 columns: FirstName, City and Age
  • The database name is: TestDatabase

The ‘Person’ table also contains the following 6 records:

How to Delete Records from a Table in SQL Server

The ultimate goal is to delete the records from the above table.

(1) Delete Records Based on Specified Conditions

You can use the following template in order to delete the records based on specified conditions:

DELETE FROM [database_name].[table_schema].[table_name]
WHERE condition

For example, let’s delete all the records where the age is lower than 40. In that case, you’ll need to add the condition of WHERE Age < 40 to the query:

DELETE FROM [TestDatabase].[dbo].[Person]
WHERE Age < 40

After running the above query, 3 records (where the age is less than 40) will be deleted from the table:

Message

To verify that the records were deleted, let’s run the following SELECT query:

SELECT * FROM [TestDatabase].[dbo].[Person]

You’ll notice that the 3 records, where the age is less than 40, were indeed deleted:

Delete Records from a Table in SQL Server

(2) Delete All the Records from a Table in SQL Server

What if you want to delete all the records from the table?

In that case, you can use the following template to delete all the records:

DELETE FROM [database_name].[table_schema].[table_name]

For our example:

DELETE FROM [TestDatabase].[dbo].[Person]

After running the above query, all the records in the ‘Person’ table will be deleted.

Let’s rerun the SELECT query:

SELECT * FROM [TestDatabase].[dbo].[Person]

As you can see, all the records under the ‘Person’ table were deleted:

Empty table

You may also want to check the following tutorial that explains how to insert new records to a table in SQL Server.