Keeping a deleted row sounds simple until an active customer wants the same email address. A soft delete with an IsDeleted flag needs rules for reads, unique keys, restoration, and archiving. Design those rules before adding the column.
State the Retention Rule
Start by defining why a row remains: legal retention, undo, audit, or application recovery. Those goals lead to different access and archive rules. A BIT flag alone records only the current state. Add a deletion timestamp and, if required, a trusted actor identifier so operators can explain when and why a row left normal views.
ALTER TABLE dbo.Customer
ADD IsDeleted bit NOT NULL
CONSTRAINT DF_Customer_IsDeleted DEFAULT (0),
DeletedAt datetime2(0) NULL;Test this on a copy because adding columns and constraints to a large table has deployment costs. I document whether deletion is reversible and what restoring an old row means for its email address or other unique business key. What should happen if a new active customer already claimed that key?
Write the Soft Delete as an UPDATE
The application should mark the row in one transaction and apply the same rule to related data. A foreign key will not cascade a soft delete the way it cascades a physical DELETE. Decide whether child rows inherit the status, remain visible through a parent filter, or are separately archived. Avoid a trigger that silently changes DELETE semantics without application agreement.
DECLARE @CustomerID int = 42;
UPDATE dbo.Customer
SET IsDeleted = 1,
DeletedAt = SYSUTCDATETIME()
WHERE CustomerID = @CustomerID
AND IsDeleted = 0;The declared ID is a test value; pass it as a parameter in an application procedure. Check @@ROWCOUNT immediately if the client needs to distinguish a newly deleted row from an already deleted or missing row. Keep authorization rules for undeleting as explicit as those for deletion. Retaining rows is only useful if access to private data remains controlled.
Keep Unique Keys Working After a Soft Delete
A plain unique index on Email across every row prevents a new active row from using an email that belongs only to a deleted record. A filtered unique index enforces uniqueness among active rows and leaves deleted rows available for history. Check the business rule first: some identifiers must remain globally unique even after deletion.
CREATE UNIQUE INDEX UX_Customer_ActiveEmail
ON dbo.Customer(Email)
WHERE IsDeleted = 0 AND Email IS NOT NULL;If an unfiltered unique constraint already exists, remove it only after you have examined duplicates and changed code that depends on it. Restoring a deleted row fails the filtered uniqueness check if its email has been reused; in my test the restore stopped with Msg 2601. I treat that as a business conflict requiring a decision, not an error to bypass with disabled constraints.
Give Readers a Safe Default
A view exposes active rows and reduces the chance that each application query forgets the filter. Grant read access through the view where practical. It is not a universal shield: ad hoc SQL, exports, joins to the base table, and privileged users can still read deleted rows. Document which paths intentionally need history.
CREATE OR ALTER VIEW dbo.ActiveCustomer
AS
SELECT CustomerID, Email, CustomerName, CreatedAt
FROM dbo.Customer
WHERE IsDeleted = 0;Choose explicit columns so a new private column does not appear automatically. If the base table grants remain broad, a view alone does not enforce access policy. I review the most-used queries with Query Store or application traces and replace table references intentionally, then test counts before and after.
Index the Common Active Query
A filtered index containing only active rows can be much smaller than an index over the full history. Put equality columns before a range when that matches the workload, and INCLUDE output-only columns when useful. The filter must be compatible with the query predicate for the optimizer to use it. Compare the actual plan and logical reads.
CREATE INDEX IX_Customer_ActiveCreated
ON dbo.Customer(CreatedAt)
INCLUDE (CustomerName,Email)
WHERE IsDeleted = 0;
SELECT CustomerID,CustomerName,CreatedAt
FROM dbo.Customer
WHERE IsDeleted = 0 AND CreatedAt >= '20250101';Record rows read and returned, not only the Index Seek label. Parameterization can affect whether a filtered index is safe for a plan that also serves IsDeleted = 1; test the actual application query. A filtered index still costs writes for rows entering or leaving its filter.
Compare Plans Before and After
Take a baseline for active-row count, reads, CPU, duration, and plan shape before changing indexes. After creating the filtered index, use the same query, parameter values, and similar data state. Check the update plan too, because changing IsDeleted removes an entry from each active-only filtered index. A large number of such indexes can make deletion expensive.
I inspect at least one query that intentionally reads history. A design that speeds active lookups but makes audits inaccessible has not met the retention goal. For a frequent history query, a separate index on DeletedAt or an archive table can be appropriate. The query pattern decides.
Archive Before Growth Becomes the Problem
Create a scheduled, small-batch archive process for rows past the approved retention point. Copy rows and required child records to a protected archive, validate counts and keys, then physically remove them from the live table in the same planned workflow. An Agent job can run that procedure nightly, but write the procedure and rehearse restore before scheduling it. Do not hard-code a retention period without the data owner's approval.
SELECT COUNT_BIG(*) AS eligible_rows
FROM dbo.Customer
WHERE IsDeleted = 1
AND DeletedAt < DATEADD(month,-18,SYSUTCDATETIME());The eighteen-month cutoff is only an example to size the task. Track archive failures and last successful run. I keep an audit of the move so a row can be found without guessing which database holds it. Privacy deletion requests can require a different path from ordinary archive.
Keep the Soft Delete Contract Consistent
Soft delete is a state transition that every writer and reader must understand. Test list pages, search, foreign-key relationships, uniqueness, restore, exports, and reports. A view and filtered indexes reduce risk, but they do not replace an inventory of direct table access.
The design succeeds when active queries are simple, history remains explainable, and old rows have a managed exit. I prefer a measured archive process over allowing a BIT column to turn the primary table into an endless attic. Preserve before and after plans so the performance tradeoff remains visible.
Related reading on this blog: Unique Indexes on Nullable Columns and Delete Statement and Index Usage.
A soft delete is not one BIT column, it is a contract for access, keys, restore, and archive.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
