Q:

SQL datatype choice for flagging a read/ unread message?

I have a table where I want to track Boolean type operations. in addition, a business requirement is that I know the date of the action and not just the Boolean value. 
for example if a message is read or deleted. 
I would like to use a date time field so that I can just quantify all unread messages as having null or a date time value for read. I get Boolean operation and a timestamps.
My only qualm about this is how efficient will selecting all unread messages be?
Lets say in a table with a few million records and a non clustered index on a field such as read would a select perform way worse than if the field were a Boolean?

A:

Performance of a particular design depends on the distribution of data to a large extent, and the access path. Are you expecting most messages to have the flag, or most messages not to? From the docs:

My tests show that a table scan often
  starts to perform better than a
  nonclustered index access when at
  least 10 percent of the rows are
  selected. I also found that the
  optimizer switches from nonclustered
  index access to table scan prematurely
  (i.e., when nonclustered index access
  still shows better response time than
  the corresponding table scan). In many
  cases, the optimizer forces a table
  scan for queries with result sets of
  approximately 5 percent, although the
  table scan becomes more efficient than
  index access at selectivities of 8 to
  10 percent.

And of course, if there are any other predicates on the query, and the clustered index if any. For example, are you likely to want to access all unread messages within a certain timeframe? Or sent to/from a particular user? How big are the message bodies and are they stored inline? These are just rhetorical questions, mind.
So which is better, a BIT and a DATETIME or just a DATETIME for performance? You will have to benchmark with some representative data and access patterns to find out. I'm afraid this one can't be answered with just theory. But you can of course create the table with the boolean in and just not use it if you find the latter is better - it only adds an overhead of 1 byte per row. 
If you were asking the opposite question, how to efficiently query the not-NULL rows, I would have said use a filtered index. 

