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
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.