1. Introduction
Soft-delete is a common requirement in enterprise applications.
Instead of physically deleting a row, you mark it as deleted using a flag like:
IsDeleted BIT DEFAULT 0
This allows:
However, soft-delete introduces a hidden performance problem:
Every SELECT query must filter by IsDeleted = 0.
In high-volume systems where tables grow into millions of rows, the additional predicate causes:
This article explains how to design efficient soft-delete models that do not degrade query performance, using SQL Server best practices.
2. Why Soft-Delete Causes Query Degradation
2.1 Index Bloat
If IsDeleted is part of the index key or included columns, the index size increases unnecessarily.
2.2 Additional Predicate in Every Query
Developers must constantly write:
SELECT ...
FROM Customers
WHERE IsDeleted = 0;
Missing the filter leads to data leaks or broken logic.
2.3 Plan Instability
The cardinality estimator struggles when filtered vs unfiltered selectivity changes over time.
2.4 Non-Selective Predicate
IsDeleted = 0 typically matches 99% of rows, so SQL Server cannot use it as an effective filter.
3. A Real-World Example
Consider a Customers table:
CREATE TABLE dbo.Customers
(
CustomerId INT IDENTITY PRIMARY KEY,
Name VARCHAR(200),
City VARCHAR(50),
Email VARCHAR(200),
IsDeleted BIT NOT NULL DEFAULT 0,
CreatedDate DATETIME2
);
Queries become slow:
SELECT CustomerId, Name, City
FROM dbo.Customers
WHERE City = 'Mumbai' AND IsDeleted = 0;
Over time, the table grows to 10 million rows, and non-deleted rows degrade into table scans.
4. The Goal
Design soft-delete in a way that:
Has minimal query overhead
Preserves efficient index seeks
Avoids index bloat
Allows fast restore
Keeps the application simple
We will use proven patterns from high-scale transactional systems.
5. Strategy 1: Filtered Indexes for Soft-Deletes
Filtered indexes make queries fast because they store only active rows.
Step 1: Create a filtered index
CREATE INDEX IX_Customers_Active_City
ON dbo.Customers (City)
WHERE IsDeleted = 0;
Benefits
Index is small (only non-deleted rows).
Seeks become extremely fast.
Query automatically uses the small filtered index.
Deleted rows do not impact performance.
Critical Note
Your SELECT queries must include:
WHERE IsDeleted = 0
for SQL Server to use the filtered index.
6. Strategy 2: Use a View to Hide IsDeleted Logic
To avoid repeating the filter in every query, create a view.
Step 1: Create view
CREATE VIEW dbo.ActiveCustomers
ASSELECT *FROM dbo.Customers
WHERE IsDeleted = 0;
Step 2: Use view everywhere
SELECT * FROM dbo.ActiveCustomers
WHERE City = 'Pune';
Benefits
Simplifies application code.
Ensures consistent filtering.
Filtered indexes work automatically.
Caution
Avoid SELECT * in production views. List explicit columns to avoid future schema conflicts.
7. Strategy 3: Move Deleted Rows to an Archive Table
Instead of keeping deleted rows in the main table, periodically archive them.
7.1 Archive Table
CREATE TABLE dbo.Customers_Archive
(
CustomerId INT PRIMARY KEY,
Name VARCHAR(200),
City VARCHAR(50),
Email VARCHAR(200),
DeletedDate DATETIME2,
OriginalCreatedDate DATETIME2
);
7.2 Archive Procedure
CREATE PROCEDURE dbo.ArchiveDeletedCustomers
ASBEGIN
SET NOCOUNT ON;
;WITH DeletedRows AS
(
SELECT *
FROM dbo.Customers
WHERE IsDeleted = 1
)
DELETE FROM DeletedRows
OUTPUT
deleted.CustomerId,
deleted.Name,
deleted.City,
deleted.Email,
SYSUTCDATETIME(),
deleted.CreatedDate
INTO dbo.Customers_Archive;
END;
Benefits
Main table stays small.
Index fragmentation drops.
Query performance stays predictable.
Purge/archive storage separately.
When to run
Nightly job
Weekends
After batch deletes
8. Strategy 4: Soft-Delete with Row-Versioning
Enable optimistic row versioning so that deleted rows do not block reads.
ALTER DATABASE MyDB
SET READ_COMMITTED_SNAPSHOT ON;
Benefit
9. Strategy 5: Partition Active vs Deleted Rows
One advanced approach is to partition the table:
Partition Function
CREATE PARTITION FUNCTION pfIsDeleted (BIT)
AS RANGE LEFT FOR VALUES (0);
Partition Scheme
CREATE PARTITION SCHEME psIsDeleted
AS PARTITION pfIsDeleted
TO ([PRIMARY], [SlowDiskFileGroup]);
Create Table on Partition Scheme
CREATE TABLE dbo.Customers
(
CustomerId INT IDENTITY PRIMARY KEY,
Name VARCHAR(200),
City VARCHAR(50),
IsDeleted BIT NOT NULL
)
ON psIsDeleted(IsDeleted);
Result
Active rows stay on fast storage.
Deleted rows go to slower storage.
Inserts, updates, and reads are efficient.
Operational queries never touch deleted data partitions.
Perfect for
10. Strategy 6: Controlled Soft-Delete Using Status Column
Instead of a BIT flag, use an ENUM-like Status:
Status SMALLINT-- 0 Active-- 1 PendingDelete-- 2 Deleted
Why?
Easier lifecycle management
Allows “Scheduled delete” flows
Avoids accidental exposure of pending-deleted records
Example Filtered Index
CREATE INDEX IX_Customers_Active_City
ON dbo.Customers (City)
WHERE Status = 0;
11. Strategy 7: Always Filter Using Computed Column
A computed column makes filtering more index-friendly.
Add computed column
ALTER TABLE dbo.Customers
ADD IsActive AS CASE WHEN IsDeleted = 0 THEN 1 ELSE 0 END PERSISTED;
Create index
CREATE INDEX IX_Customers_IsActive_City
ON dbo.Customers (IsActive, City)
WHERE IsActive = 1;
Benefits
12. Choosing Best Strategy for Your System
| Workload Type | Best Strategy |
|---|
| Heavy OLTP with millions of rows | Archive + filtered indexes |
| Dashboard analytics | View-based soft-delete |
| High retention policies | Partitioned tables |
| Compliance-heavy apps | Status-based lifecycle |
| Large-scale deletes | Row-versioning ON + archive |
| High concurrency | Filtered indexes + computed column |
13. Recommended Best Practices
13.1 Never include IsDeleted in clustered index
It destroys index order.
13.2 Always use filtered indexes
They reduce storage and improve seek performance.
13.3 Use views to enforce soft-delete consistently
Reduces risk of missing predicate.
13.4 Use archive tables
Keeps OLTP table slim and fast.
13.5 Schedule deletion jobs during off-hours
Avoids concurrency hotspots.
13.6 Monitor fragmentation
Soft deletes cause page splits and fragmentation.
14. Example: Full Production-Ready Solution
Combining best strategies:
Step 1: Add IsDeleted
ALTER TABLE dbo.Customers ADD IsDeleted BIT DEFAULT 0;
Step 2: Computed Column
ALTER TABLE dbo.Customers
ADD IsActive AS CASE WHEN IsDeleted = 0 THEN 1 END PERSISTED;
Step 3: Filtered Index
CREATE INDEX IX_Customers_Active
ON dbo.Customers (IsActive, City)
WHERE IsActive = 1;
Step 4: Active View
CREATE VIEW dbo.ActiveCustomers
ASSELECT CustomerId, Name, City, Email
FROM dbo.Customers
WHERE IsDeleted = 0;
Step 5: Archive Job
EXEC dbo.ArchiveDeletedCustomers;
Outcome
Conclusion
Soft-delete is simple conceptually, but it can destroy SQL Server query performance if not designed correctly.
Using filtered indexes, views, computed columns, partitioning, and periodic archiving ensures that your application gets the advantages of soft-delete without the performance penalties.
In well-designed systems:
Active rows stay small and indexed
Deleted rows stay out of critical paths
SELECT performance remains stable
Storage is optimized
Compliance and audit requirements are met
This combination gives the best balance between performance, safety, and maintainability in enterprise applications.