DELETE and TRUNCATE: Remove data with different scopes

Deleting information requires distinguishing two intentions: deleting selected rows or emptying an entire table. DELETE and TRUNCATE TABLE can leave a table empty of data, but they do not follow the same rules or provide the same control.

The idea

DELETE: remove selected rows

DELETE removes rows that meet a condition and can be used within a transaction. Without WHERE, it attempts to remove every row from the table.

DELETE FROM dbo.Tabla
WHERE Condicion;

The condition defines the scope. We should therefore check it first with a SELECT that uses the same filter.

TRUNCATE TABLE: empty the complete table

TRUNCATE TABLE empties the entire table. It does not accept WHERE, uses a different page release mechanism, and resets the counter of a IDENTITY column. It also has restrictions when relationships, replication or other features exist.

TRUNCATE TABLE dbo.Tabla;

Its unit of work is the complete table. It cannot select individual rows and does not replace a filtered deletion.

Example: identify before delete

We want to remove old movements from a work table. First we count and review a sample:

DECLARE @FechaLimite DATETIME2(0) = '2026-01-01';

SELECT COUNT(*) AS FilasPorEliminar
FROM dbo.MovimientoInventario
WHERE FechaMovimiento < @FechaLimite;

SELECT TOP (20)
    IdMovimiento,
    IdProducto,
    FechaMovimiento,
    Referencia
FROM dbo.MovimientoInventario
WHERE FechaMovimiento < @FechaLimite
ORDER BY FechaMovimiento;

DECLARE defines the limit as a local variable; its use is explained in DECLARE, SET, and SELECT.

Then we apply the same condition:

DELETE FROM dbo.MovimientoInventario
WHERE FechaMovimiento < @FechaLimite;

SELECT @@ROWCOUNT AS FilasEliminadas;

Try a manual removal

A transaction groups changes that can be committed or rolled back as one unit. This lets us inspect the result before deciding:

BEGIN TRANSACTION;

DELETE FROM dbo.MovimientoInventario
WHERE IdMovimiento = 1;

SELECT @@ROWCOUNT AS FilasEliminadas;

-- Sustituir por COMMIT únicamente después de validar.
ROLLBACK;

The transaction does not replace a backup—a recoverable copy of the data—or a correct condition. It only keeps the option to roll back open while it remains active.

When does TRUNCATE TABLE appear

If a temporary loading table must be completely empty before the next process, we could use:

TRUNCATE TABLE dbo.CargaTemporal;

It is not a fast variant of DELETE WHERE. Its scope is always the entire table, and it does not fire DELETE triggers, the automatic code associated with a deletion. Although it can be rolled back when executed within a transaction in SQL Server, we must treat it as a high-impact operation.

Main differences

What should be checked

Before deleting, we check dependencies, foreign keys, triggers, required retention, and the existence of a usable backup. A foreign key requires a reference to point to a valid row and may prevent that row from being deleted. We also need to know whether another session can insert or change rows between validation and deletion.

For large volumes, batch deletion—several small deletions instead of one large operation—can reduce growth of the transaction log, where SQL Server records changes, and shorten lock duration. It still requires a stable condition and progress tracking.

Related to

• WHERE: Limit rows deleted by DELETE.
• Transactions: allow you to confirm or reverse within your scope.
• Foreign keys: can prevent deletion of referenced rows.
• OUTPUT: You can capture information about deleted rows.