Should there be an index on 'Deleted' flags?

Question

Within my SQL Server 2008 R2 database, we make use of Soft Deletes (A nullable DateTime column, called 'Deleted').

This is on every table in our database.

I have been adding indexes, mainly based on larger tables, on columns commonly used in the WHERE clauses, or on JOIN ON clauses.

My question, though, is, should I be including the DELETED column in all these indexes as well, because most of the time, I might do something like:

SELECT ....
FROM TableA a
INNER JOIN TableB b
ON b.TableAId = a.Id
AND b.Deleted IS NULL
WHERE a.AFieldId = x

In that case, would it be beneficial to have the index like this:

CREATE NONCLUSTERED INDEX idx_TableA
ON [dbo].[Tableb] (TableAId, [Deleted])

And therefore, as MOST of my queries check deleted flags, they should all be like this?

Answer 1

You can reduce the size of an index using a filtered index:

CREATE NONCLUSTERED INDEX idx_TableA
ON [dbo].[Tableb] (TableAId)
WHERE ( [Deleted] IS NULL )

Of course, the most applicable index is almost always a covering index:

CREATE NONCLUSTERED INDEX idx_TableA
ON [dbo].[Tableb] (TableAId)
INCLUDE ( column list )
WHERE ( [Deleted] IS NULL )

Ref.: CREATE INDEX

Create Filtered Indexes