Delete Data

Deleting data is the most irreversible operation in SQL Server. An INSERT can be undone with a DELETE. An UPDATE can be reversed by updating back. But a DELETE without a transaction and without a backup has no undo — the rows are gone. Understanding exactly how DELETE works, the difference between DELETE and TRUNCATE, how foreign key constraints govern deletion order, and how to use transactions as a safety net is not optional knowledge for anyone writing SQL against a production database. This lesson covers every deletion pattern in SQL Server, the scenarios where each is appropriate, and the habits that prevent catastrophic mistakes — using the DataplexaStore tables throughout.

Basic DELETE

The DELETE statement removes rows from a table that match a WHERE condition. Like UPDATE, the WHERE clause is what makes DELETE safe and targeted. Without a WHERE clause DELETE removes every row in the table — the table structure, constraints, and indexes remain, but every row of data is gone. The golden rule before any DELETE is identical to UPDATE: run the equivalent SELECT with the same WHERE clause first, verify the row count and the specific rows returned, and only then run the DELETE. SQL Server's (n rows affected) message after a DELETE is a confirmation, not a preview — by the time you read it the rows are already gone.

-- Safe DELETE pattern — always SELECT first with the same WHERE clause
USE DataplexaStore;

-- Step 1: verify which row will be deleted
SELECT ReviewID, UserID, ProductID, Rating, ReviewText
FROM   Reviews
WHERE  ReviewID = 4;

-- Step 2: delete only after confirming the SELECT result
DELETE FROM Reviews
WHERE  ReviewID = 4;

-- Step 3: confirm the row is gone
SELECT ReviewID FROM Reviews WHERE ReviewID = 4;

-- Verify the remaining reviews
SELECT ReviewID, UserID, ProductID, Rating
FROM   Reviews
ORDER BY ReviewID;

• ReviewID 4 is gone permanently — the IDENTITY counter does not reuse it; the next review inserted will receive ReviewID 6, leaving a permanent gap at 4
• Filtering by primary key (ReviewID = 4) is the safest form of DELETE — it targets exactly one row with no ambiguity and uses the clustered index directly
• The (0 rows affected) on the post-delete SELECT confirms the row no longer exists — any application or query that references ReviewID 4 will find nothing

DELETE with Foreign Key Constraints — Dependency Order

Foreign key constraints protect referential integrity during deletion just as they do during insertion — but the direction is reversed. You cannot delete a parent row while child rows still reference it. Attempting to delete a User who has Orders fails with Msg 547 because Orders has a foreign key to Users. The deletion order must follow the dependency chain in reverse: delete child rows first, then parent rows. In DataplexaStore the correct deletion order for a user and all their data is: Reviews first, then OrderItems, then Orders, then the User. This ensures no orphaned child rows remain and no foreign key constraint is violated at any step.

-- Demonstrate foreign key protection on DELETE
-- Attempt to delete a User who has Orders — must fail

DELETE FROM Users WHERE UserID = 1;   -- Alice has Orders, this must be rejected

-- Correct approach: delete in reverse dependency order
-- First check what data exists for UserID 1
SELECT 'Orders'     AS table_name, COUNT(*) AS rows FROM Orders     WHERE UserID = 1 UNION ALL
SELECT 'Reviews',                  COUNT(*)          FROM Reviews    WHERE UserID = 1;

-- Step 1: delete Reviews referencing UserID 1
DELETE FROM Reviews    WHERE UserID = 1;

-- Step 2: delete OrderItems for orders belonging to UserID 1
DELETE FROM OrderItems
WHERE  OrderID IN (SELECT OrderID FROM Orders WHERE UserID = 1);

-- Step 3: delete Orders belonging to UserID 1
DELETE FROM Orders     WHERE UserID = 1;

-- Step 4: now the User can be safely deleted
DELETE FROM Users      WHERE UserID = 1;

-- Confirm UserID 1 and all related data is gone
SELECT COUNT(*) AS remaining_alice_rows
FROM   Users WHERE UserID = 1;

• Msg 547 on the first attempt confirms foreign key protection is working — the database prevented an orphaned Orders situation before we could create it
• The subquery in the OrderItems DELETE (WHERE OrderID IN (SELECT OrderID FROM Orders WHERE UserID = 1)) correctly identifies all line items belonging to Alice's orders without needing to know the specific OrderIDs in advance
• This four-step pattern is the correct manual cascade — in contrast, ON DELETE CASCADE would do this automatically but silently, which is why NO ACTION is safer for business data where deletions should be deliberate and auditable

DELETE with OUTPUT — Capturing Deleted Rows

The OUTPUT clause works on DELETE exactly as it does on UPDATE, capturing the deleted rows before they are removed. OUTPUT DELETED returns every column from every row that was deleted, either as a result set or redirected into a table variable for logging. This is the correct pattern for soft-delete migration — reading the rows out as you delete them so they can be inserted into an archive table in the same operation, with no risk of data being deleted without being captured first.

-- DELETE with OUTPUT — capture deleted rows for archiving before they are removed

-- Create a simple archive table for deleted reviews
CREATE TABLE Reviews_Archive (
    ReviewID    INT             NOT NULL,
    UserID      INT             NOT NULL,
    ProductID   INT             NOT NULL,
    Rating      TINYINT         NOT NULL,
    ReviewText  NVARCHAR(1000)  NULL,
    ReviewDate  DATE            NOT NULL,
    DeletedAt   DATETIME2       NOT NULL DEFAULT GETDATE(),
    CONSTRAINT PK_Reviews_Archive PRIMARY KEY (ReviewID)
);

-- Delete low-rated reviews and archive them simultaneously
DELETE FROM Reviews
OUTPUT
    DELETED.ReviewID,
    DELETED.UserID,
    DELETED.ProductID,
    DELETED.Rating,
    DELETED.ReviewText,
    DELETED.ReviewDate
INTO Reviews_Archive (ReviewID, UserID, ProductID, Rating, ReviewText, ReviewDate)
WHERE Rating <= 3;              -- remove reviews rated 3 or below

-- Confirm Reviews table
SELECT ReviewID, Rating FROM Reviews ORDER BY ReviewID;

-- Confirm archive table captured the deleted rows
SELECT ReviewID, Rating, ReviewText, DeletedAt
FROM   Reviews_Archive
ORDER BY ReviewID;

• ReviewID 4 (Rating 3) was already deleted earlier in this lesson, so only ReviewID 2 (Rating 4) remained as a candidate for the Rating <= 3 filter — the three 5-star reviews stayed in the main table
• OUTPUT DELETED and INTO operate atomically — there is no moment where the row is deleted from Reviews but not yet in Reviews_Archive; both happen in the same statement
• The DeletedAt column in Reviews_Archive was populated by its DEFAULT GETDATE() — no explicit value was needed in the INTO column list because it was omitted, triggering the default

DELETE with JOIN — Removing Rows Based on Another Table

Like UPDATE, DELETE supports a FROM clause with JOIN to filter the rows being deleted based on conditions in a related table. This is useful when the criteria for which rows to delete comes from a different table — for example deleting all order items for cancelled orders, or removing reviews for discontinued products. The T-SQL syntax places the JOIN in a FROM clause after the DELETE keyword, and the alias of the table being deleted appears in the DELETE clause.

-- DELETE with JOIN — remove OrderItems for orders with a specific status
-- Re-insert some test data first so we have rows to demonstrate with

INSERT INTO Orders  (UserID, TotalAmount, Status)
VALUES (2, 15.00, 'cancelled');

INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
VALUES (SCOPE_IDENTITY(), 8, 1, 12.99),   -- Notebook for the cancelled order
       (SCOPE_IDENTITY(), 9, 1,  8.99);   -- Pen set for the cancelled order

-- Verify the test data
SELECT o.OrderID, o.Status, oi.OrderItemID, oi.ProductID
FROM   Orders o
JOIN   OrderItems oi ON o.OrderID = oi.OrderID
WHERE  o.Status = 'cancelled';

-- Delete OrderItems for all cancelled orders using JOIN
DELETE oi
FROM   OrderItems oi
JOIN   Orders     o  ON oi.OrderID = o.OrderID
WHERE  o.Status = 'cancelled';

-- Then delete the cancelled orders themselves
DELETE FROM Orders WHERE Status = 'cancelled';

-- Confirm clean state
SELECT COUNT(*) AS remaining_cancelled FROM Orders WHERE Status = 'cancelled';

• DELETE oi specifies the alias of the table being deleted — the JOIN brings in Orders to provide the Status filter, but only rows from OrderItems (aliased as oi) are removed
• SCOPE_IDENTITY() after the Orders INSERT captured the new OrderID (6) so the OrderItems INSERT could reference it immediately without a separate SELECT
• The two-step pattern — delete children first, then parents — mirrors the manual cascade required by the NO ACTION foreign key setting

TRUNCATE TABLE — Bulk Row Removal

TRUNCATE TABLE removes all rows from a table faster than DELETE by operating as a minimally logged metadata operation rather than logging each individual row deletion. It deallocates the data pages used by the table and resets any IDENTITY column counter back to its seed value. TRUNCATE is dramatically faster than DELETE for large tables — a DELETE of one million rows generates one million log records; TRUNCATE generates a handful. However, TRUNCATE has significant restrictions that make it less flexible than DELETE: it cannot be used on tables referenced by foreign key constraints (even if no child rows currently exist), it cannot include a WHERE clause (it always removes all rows), and while it is transaction-safe it is harder to recover from accidentally than a DELETE inside an explicit transaction.

-- TRUNCATE vs DELETE — behaviour comparison

-- Create a test table to demonstrate TRUNCATE safely
CREATE TABLE TruncateDemo (
    ID      INT IDENTITY(1,1) PRIMARY KEY,
    Value   NVARCHAR(50)
);

INSERT INTO TruncateDemo (Value) VALUES ('Row A'), ('Row B'), ('Row C');

-- Check IDENTITY state before TRUNCATE
SELECT last_value FROM sys.identity_columns
WHERE  OBJECT_NAME(object_id) = 'TruncateDemo';

-- TRUNCATE — removes all rows and resets IDENTITY counter
TRUNCATE TABLE TruncateDemo;

-- IDENTITY counter resets to seed value
SELECT last_value FROM sys.identity_columns
WHERE  OBJECT_NAME(object_id) = 'TruncateDemo';

-- New inserts start from seed again
INSERT INTO TruncateDemo (Value) VALUES ('New Row');
SELECT * FROM TruncateDemo;

-- Attempt TRUNCATE on a table with a foreign key reference — must fail
TRUNCATE TABLE Users;   -- Users is referenced by Orders FK

-- Clean up
DROP TABLE TruncateDemo;

• last_value = NULL after TRUNCATE confirms the IDENTITY counter was reset — the next INSERT receives ID 1 again, starting from scratch as if the table was just created
• Msg 4712 on the Users TRUNCATE confirms the foreign key protection — even though all child rows were deleted earlier in this lesson, the FK constraint definition still exists on Orders, blocking TRUNCATE
• TRUNCATE cannot be used in a WHERE clause — it is always all-or-nothing; if you need to remove a subset of rows, DELETE with a WHERE clause is the only option

DELETE vs TRUNCATE — Choosing the Right Tool

The choice between DELETE and TRUNCATE comes down to three questions: do you need to filter which rows are removed, does the table have foreign key references, and do you need the IDENTITY counter preserved? If any answer is yes, use DELETE. If all answers are no and you want to clear an entire table as fast as possible — typically in development, testing, or ETL staging scenarios — TRUNCATE is the right choice. In production, TRUNCATE on a core business table should always be treated with extreme caution because it cannot be reversed without a backup restore, and it resets the IDENTITY counter which can cause confusion if downstream systems cached the old IDs.

-- Comparison: DELETE all rows vs TRUNCATE — what each leaves behind

-- DELETE all rows from a staging table (no FK references)
CREATE TABLE StagingOrders (
    StagingID   INT IDENTITY(1,1) PRIMARY KEY,
    RawData     NVARCHAR(500)
);

INSERT INTO StagingOrders (RawData) VALUES ('order 1'), ('order 2'), ('order 3');

-- DELETE all — IDENTITY continues from where it was
DELETE FROM StagingOrders;
INSERT INTO StagingOrders (RawData) VALUES ('order 4');
SELECT * FROM StagingOrders;           -- StagingID will be 4, not 1

-- TRUNCATE — IDENTITY resets
TRUNCATE TABLE StagingOrders;
INSERT INTO StagingOrders (RawData) VALUES ('order 5');
SELECT * FROM StagingOrders;           -- StagingID will be 1, not 5

DROP TABLE StagingOrders;

• DELETE all rows leaves the IDENTITY counter at its last value (3) — the next insert gets 4, maintaining continuity with the IDs that existed before the delete
• TRUNCATE resets the counter to the seed — the next insert gets 1, as if the table is brand new; any external system that stored IDs 1, 2, or 3 would now find those IDs pointing to completely different rows
• For ETL staging tables that are cleared and reloaded every day, TRUNCATE is the correct choice — it is faster, simpler, and the ID continuity does not matter for temporary staging data

Summary Table

Practice Questions

Quiz

Next up - Aggregate Functions - Summarise your data with COUNT, SUM, AVG, MIN, and MAX and group results to reveal patterns across the DataplexaStore.