Sample Header Ad - 728x90

Can I use SPARSE somehow on a non-nullable bit column with mostly false values?

1 vote
1 answer
124 views
I have a table that stores the results of queries that are run at least once a day. There's a bit column that represents whether the row is from the *most recent* run of a particular query with a particular set of arguments. (Yes, it's a functional dependency, but a necessary denormalization for performance, since most queries on this table are only interested in the most recent result.) Since this bit column is almost always a false value, I'm looking for the best way to tune queries returning only the true values. Partitioning isn't an option (Standard Edition). It seems like making the column SPARSE would be an interesting solution, but I believe that would require me to change the column to nullable and use NULL rather than 0 for false values. Seems a little kludgy. Is there an option similar to SPARSE that would optimize space/performance for a non-null bit column with mostly (well over 99%) false values? Pinal Dave's article indicates that both zero and null values are optimized, but this doesn't seem right to me, since these are different values -- unless MSSQL is using the same mechanism for non-null columns to indicate the default value. This would be great if it were true, but the BOL doesn't mention this.
Asked by richardtallent (352 rep)
Jan 25, 2015, 03:44 AM
Last activity: Aug 23, 2018, 09:20 AM