title: Are there performance concerns with querying a varchar(1) column vs. a bit flag and datetime comparison?
body: <p>Using MS SQL 2008, all tables contain a Status varchar(1) column that indicates "I" for inserted record, "U" for updated record, and "D" for deleted record as well as a DateCreated datetime column and a DateUpdated datetime column.</p>
  
  <p>In most cases, we want to query tables for active records only and we would do something like: </p>
  
  <pre><code>SELECT column FROM table WHERE Status &lt;&gt; 'D'
  </code></pre>
  
  <p>To provide a perspective on usage, this is most frequently used filter as it appears in nearly every query and multiple times when tables are joined.</p>
  
  <p>We're developing a new web application and database with a focus on maximizing performance.  One proposal is to, starting with this and future projects, have the varchar(1) Status column pattern replaced with a bit like "IsDeleted" to indicate if the record was deleted or not and infer updated status from the two datetime fields.</p>
  
  <p>In other words...</p>
  
  <pre><code>SELECT column as InsertedRecords FROM table WHERE Status = 'I' -- Rare case
  SELECT column as UpdatedRecords FROM table WHERE Status = 'U' -- Rare case
  SELECT column as ActiveRecords FROM table WHERE Status &lt;&gt; 'D'
  SELECT column as DeletedRecords FROM table WHERE Status = 'D'
  </code></pre>
  
  <p>...would instead look something like...</p>
  
  <pre><code>SELECT column as InsertedRecords FROM table WHERE IsDeleted = 0 AND DateCreated = DateUpdated -- Rare case
  SELECT column as UpdatedRecords FROM table WHERE IsDeleted = 0 AND DateCreated &lt;&gt; DateUpdated -- Rare case
  SELECT column as ActiveRecords FROM table WHERE IsDeleted = 0
  SELECT column as DeletedRecords FROM table WHERE IsDeleted = 1
  </code></pre>
  
  <p>Are there any tangible performance benefits/implications (primary around indexes and large queries) or are both implementations perfectly acceptable?  Are there any disadvantages with continuing the current pattern for consistency sake to align them with the previously created applications/databases?</p>