edit: SQL Server - I'm hoping this is a generic enough question I need not specify version, but most of the instances I'm working with are 2012 or later.
I am not good enough to mock up data and actually test this, so I'm hoping someone could look at it and answer with simple experience.
Imagine you have a state table with American state abbreviations in a column (and it's indexed, like a good lookup column). When writing ad-hoc queries, users will often hit up this column to filter on, but using criteria that represents information that is not implicit in the database.
For example, if they want to get "big states" they may include a filter in their ad-hoc query that shows something like
... where StateAbbreviation in ('AK', 'TX')
aka the dreaded "Business Rules"
So this query is fine, it performs well, and makes use of the index. But, man, what a bear to write every time we need to query "the big states." I'm tempted to create a view with this filter in it's definition to make it easier on them.
The problem here is that those business rules are specific to some number of ad-hoc queries supporting a line of business, but don't really have universal uses. So creating a view that filters out data in this way will have little utility.
So instead of writing a view where that criteria is in the filter, I want to write a view where the criteria result is calculated, such as
select StateAbbreviation , IsBig = case when StateAbbreviation in ('AK' , 'TX') then 1 else 0 end from tblStates
Now, when they want to write a query for big states, they just include
where IsBig = 1
in the query.
So, my question is simple - if the view were called with that criteria, can the index on StateAbbreviation be used?
I know there are all kinds of things I could do inside a CASE statement that may change the answer, so for the purposes of answering this very specific question, assume the case statement will only ever look like that. It will not make use of multiple fields, it will not aggregate - just a simple in or out calculation of literals to expose complex filter criteria to report writers in a simpler way.