title: Should there be an index on 'Deleted' flags?
body: <p>Within my SQL Server 2008 R2 database, we make use of Soft Deletes (A nullable DateTime column, called 'Deleted').</p>
  
  <p>This is on every table in our database.</p>
  
  <p>I have been adding indexes, mainly based on larger tables, on columns commonly used in the <code>WHERE</code> clauses, or on <code>JOIN ON</code> clauses.</p>
  
  <p>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:</p>
  
  <pre><code>SELECT ....
  FROM TableA a
  INNER JOIN TableB b
  ON b.TableAId = a.Id
  AND b.Deleted IS NULL
  WHERE a.AFieldId = x
  </code></pre>
  
  <p>In that case, would it be beneficial to have the index like this:</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX idx_TableA
  ON [dbo].[Tableb] (TableAId, [Deleted])
  </code></pre>
  
  <p>And therefore, as MOST of my queries check deleted flags, they should all be like this?</p>