>SQL Server only allows a maximum of 16 fields to be part of an index. Has anyone found a way to workaround that or an approach that could be considered to target at being optimized over 16 fields?
>

I'm not aware of any way to get around the limit on the # of columns in an index.

But here's a question - have you ever profiled the existing queries to see which columns are being used most often? You mentioned a little over two dozen possible options for users. I wouldn't be surprised if a much smaller # (certainly lower than 16 ) would account for a large % of the total user queries.

Perhaps you could combine that information with a scenario of a two-pass process....to first query on the columns where optimization can occur.

Just a thought. But when offering that many columns for someone to filter on, I think it's worth profiling how often they get used.