SQL Server

DELETE Basics in SQL Server

Learn how to delete SQL Server rows carefully: preview them with SELECT, use WHERE, check @@ROWCOUNT, and verify removal.

By JaviPublished 12 min readBeginner

DELETE removes rows from a table. It is different from UPDATE, which keeps a row but changes one or more values. Because a delete removes the selected records, begin by writing a SELECT with the exact condition you plan to use. Inspect the rows, confirm the database, then perform the change only in the synthetic JaviBuildsLab practice environment. Never test these statements against a production system.

Preview the target rows with SELECT

Lesson 16 introduced customer 11 as a practice record. If you added that row, preview its identity before deleting anything:

SQL query: Preview the target rows with SELECTT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName
FROM dbo.Customers
WHERE CustomerId = 11;
T-SQL6 linesRead-only query display. It is not connected to a database.

Confirm that the result is the exact practice record you intend to remove and that it returns one row. If no row appears, there is nothing to delete; do not change the key just to make the next statement return something. If more than one row appears, the predicate is not specific enough. The SELECT is your chance to notice an incorrect database, unexpected match, or missing practice row while no data has been changed.

Only after that inspection, use the same WHERE condition in DELETE:

SQL query: Preview the target rows with SELECT (2)T-SQL
DELETE FROM dbo.Customers
WHERE CustomerId = 11;
T-SQL2 linesRead-only query display. It is not connected to a database.

DELETE FROM names the table from which rows will be removed. WHERE selects the target records. Keeping the predicate identical to the preview makes the relationship easy to review. This example deletes the optional row from lesson 16; it does not remove any of the original customers. DELETE removes the whole row, not just the columns selected in the preview.

WHERE is essential

A precise WHERE condition tells SQL Server which rows to remove. The WHERE lesson explains how a filter chooses rows. A primary key is a useful choice when the task concerns one row. For a condition that can match several rows, first run the SELECT with the exact condition and inspect all results. Do not rely on a remembered count or an approximate description such as “the old ones.” If the preview is broader than intended, refine it and preview again before deleting.

Dangerous demonstration only — do not run. Without WHERE, this statement targets every row in dbo.Customers:

SQL query: WHERE is essentialT-SQL
DELETE FROM dbo.Customers;
T-SQL1 lineRead-only query display. It is not connected to a database.

This is a warning example, not a suggested cleanup command. A missing filter can remove every customer row. Even when a command has a WHERE clause, a broad or incorrect condition can still remove the wrong records. The safe sequence is to write the condition as SELECT, inspect the returned rows, and only then use the same condition for DELETE.

Check the affected count and verify removal

Read @@ROWCOUNT immediately after DELETE if you want to confirm how many rows the statement affected:

SQL query: Check the affected count and verify removalT-SQL
DELETE FROM dbo.Customers
WHERE CustomerId = 11;

SELECT @@ROWCOUNT AS RowsDeleted;
T-SQL4 linesRead-only query display. It is not connected to a database.

It reports the number of rows affected by the immediately preceding statement. Put the check directly after DELETE because a later statement updates @@ROWCOUNT. If you expected one and see zero or several, stop and investigate. The count helps confirm scope, but it does not identify which records were removed or whether the right ones were targeted.

Follow with a SELECT using the same key:

SQL query: Check the affected count and verify removal (2)T-SQL
SELECT CustomerId, FirstName, LastName
FROM dbo.Customers
WHERE CustomerId = 11;
T-SQL3 linesRead-only query display. It is not connected to a database.

No row should return after a successful deletion. If the row still appears, check the SSMS messages, current database, and filter. If a different row appears, the condition did not identify the record you meant. This check is especially valuable after a command that affects multiple rows: compare the before and after results rather than interpreting a count alone.

The demo database connects dbo.Orders.CustomerId to dbo.Customers.CustomerId with a foreign key. Several customers have orders. SQL Server will normally reject an attempt to delete one of those parent customer rows while related order rows still reference it. This prevents an order from pointing to a customer that no longer exists. The schema does not define cascading deletion, and this lesson does not add it.

You can inspect the relationship before considering a delete:

SQL query: Foreign keys and related rowsT-SQL
SELECT c.CustomerId, c.FirstName, c.LastName, o.OrderId
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId
WHERE c.CustomerId = 1;
T-SQL4 linesRead-only query display. It is not connected to a database.

The result shows customer 1 alongside each referencing order. The JOIN lesson explains how rows from these tables relate. If a delete fails because of a foreign key, treat that error as information about dependent data. Do not disable the constraint or invent a cascade to force the statement through. Understanding which records depend on the parent belongs to deciding whether the change is correct.

DELETE and TRUNCATE are different

DELETE can use WHERE to target selected rows. TRUNCATE removes all rows from a table and behaves differently; it is not part of this beginner workflow. This lesson does not ask you to run it. When learning targeted row removal, focus on SELECT preview, the same WHERE condition, affected-row verification, and a final SELECT.

The SELECT list controls which columns you see; it does not limit which parts of a row DELETE removes. In the preview, you might show only CustomerId and a name so the record is recognizable. Once deleted, the entire customer row is gone, including City, State, Email, and CreatedDate. There is no column list after DELETE FROM. This is another reason to inspect the record with enough identifying information before executing the statement.

Deleting a row also does not mean that SQL Server has decided the information is no longer needed. In this demo, an order can depend on its customer through the foreign key. Other applications may have different rules, audit requirements, or retention obligations. This beginner lesson does not set such policies. It teaches you to notice the relationship and stop when you do not know whether the row should be removed. Do not remove related orders just to make a customer delete succeed.

For a group deletion, the same review applies to every candidate row, not only the first one displayed. Sort or select identifying columns if that helps your review, but keep the WHERE clause unchanged between preview and deletion. If the result set is too large to inspect confidently, pause and ask for a reviewed process; do not replace inspection with an assumed count. An unexpected zero count is also a reason to stop, because it can indicate the wrong database, a missing practice row, or a condition that does not match the intended target.

After a successful DELETE, the verification SELECT returns no matching row, but it does not show what values were removed. During a transaction-based practice, the pre-delete SELECT and the temporary result inside the transaction make the before-and-after comparison possible. In a real approved change, preserve whatever review evidence your organization’s process requires. This course keeps examples small and synthetic so a beginner can follow the scope without relying on hidden production context.

If SQL Server rejects a deletion because of a constraint, do not treat the error as an obstacle to bypass. A constraint explains that another row depends on the one you selected. Review the relationship with a SELECT or JOIN, and ask whether the intended change is actually to remove a different, dependent record. The JOIN concepts help you read this relationship, while the safe change workflow explains how to preview, count, test, and roll back a practice change.

Keep a deletion example separate from an INSERT example when learning. If you insert customer 11 and later delete it, the key becomes available again only after the deletion is committed; while the deletion is inside a transaction, the transaction still controls the change. The rollback-first exercise intentionally restores the row. Before you reuse that ID, run the preview again and confirm the state you are starting from. This avoids treating an earlier exercise as though it left the database in a known condition.

Common mistakes and practice

Common mistakes include omitting WHERE, using the wrong key, deleting a parent row that is referenced by orders, skipping the preview, ignoring the affected count, running in the wrong database, and assuming a deletion is easily reversible. A transaction can allow you to roll back an uncommitted practice change, but once a transaction is committed, rollback is no longer an undo button. That is why the next lesson focuses on safe UPDATE and DELETE practices.

Practice only in JaviBuildsLab. The rollback-first example previews customer 11, performs the deletion inside a transaction, checks the count immediately, verifies the temporary result, and then restores the row:

SQL query: Common mistakes and practiceT-SQL
SELECT CustomerId, FirstName, LastName
FROM dbo.Customers
WHERE CustomerId = 11;

BEGIN TRANSACTION;

DELETE FROM dbo.Customers
WHERE CustomerId = 11;

SELECT @@ROWCOUNT AS RowsDeleted;
SELECT CustomerId, FirstName, LastName FROM dbo.Customers WHERE CustomerId = 11;

ROLLBACK TRANSACTION;

SELECT CustomerId, FirstName, LastName FROM dbo.Customers WHERE CustomerId = 11;
T-SQL15 linesRead-only query display. It is not connected to a database.

The final SELECT should show the row again if it existed at the start. If customer 11 was absent, DELETE affects zero rows; rollback leaves the database as it was. Do not replace the ID with a seeded customer unless you first inspect related orders and keep the practice inside a transaction that you roll back.

  1. Write a SELECT that previews customer 11 before removal.
  2. What does DELETE without WHERE target?
  3. Why might a DELETE for customer 1 fail?
  4. What does @@ROWCOUNT tell you, and when should you read it?
  5. After a successful DELETE of customer 11, what should the verification SELECT return?

Answers

SQL query: AnswersT-SQL
-- 1
SELECT CustomerId, FirstName, LastName FROM dbo.Customers
WHERE CustomerId = 11;

-- 4
DELETE FROM dbo.Customers WHERE CustomerId = 11;
SELECT @@ROWCOUNT AS RowsDeleted;

-- 5
SELECT CustomerId, FirstName, LastName FROM dbo.Customers
WHERE CustomerId = 11;
T-SQL11 linesRead-only query display. It is not connected to a database.

Without WHERE, DELETE targets every row in the table. Customer 1 may be referenced by rows in dbo.Orders, so the foreign key can prevent its removal. @@ROWCOUNT is the affected-row count and should be read immediately after DELETE. A successful removal leaves no row for ID 11. In any exercise that changes data, use a transaction and prefer ROLLBACK while learning.

  1. SELECT target rows
  2. Confirm exact match
  3. DELETE with same WHERE
  4. Check rows affected
  5. Verify removal
Preview and confirm before removing rows

Return to the SQL Server Foundations roadmap to continue through the beginner path.

Check what you learned

2 quick questions

Question 1 of 2What does DELETE remove?
Question 2 of 2What is the key safety check before DELETE?

SQL Server Foundations

Lesson 18 of 25

Continue to next lesson

Safe UPDATE and DELETE Practices in SQL Server

Continue to Lesson 19

Tags

  • SQL Server
  • SQL Fundamentals
  • SSMS
  • T-SQL