SQL datatype choice for flagging a read/ unread message?

Question

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?

Answer 1

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.

Answer 2

Look into creating the index with included columns. Check this link. If you want to select all the unread messages and see their dates, create the index on the READ column and include the DATE column. If you want to select a date range and count the number of read/unread messages, then create the index on the DATE field and include the READ column. Also, you can set the Boolean READ column with a default value (lets say 0) that corresponds to unread, instead of using NULL. I am not sure if there will be any performance benefits to this though.