5

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.

8

From experience I would expect the index not to be used in the query against the view. However, to determine if something is SARGable you just need an example query against a data set for which the index is beneficial. So, best to test it.

First I'll create some test data:

CREATE TABLE dbo.tblStates (
StateAbbreviation VARCHAR(2) NOT NULL,
FLUFF VARCHAR(10) NOT NULL
);

-- insert all possible abbreviations to future-proof table
INSERT INTO dbo.tblStates WITH (TABLOCK)
SELECT CHAR(t1.number) + CHAR(t2.number), REPLICATE('Z', 10)
FROM
(
    SELECT number
    from master..spt_values
    WHERE number BETWEEN 65 and 90
) t1
CROSS JOIN 
(
    SELECT number
    from master..spt_values
    WHERE number BETWEEN 65 and 90
) t2;

CREATE INDEX IX_TBL_STATES ON dbo.tblStates (StateAbbreviation);

When running this query we can see that the index is used to do a seek:

SELECT StateAbbreviation
FROM dbo.tblStates
where
StateAbbreviation in ('AK', 'TX');

enter image description here

However, if we create a view:

CREATE VIEW STATE_VIEW
AS
select 
  StateAbbreviation
  , IsBig = case when StateAbbreviation in ('AK' , 'TX') then 1 else 0 end
from tblStates;

The equivalent query no longer uses the index to seek:

SELECT StateAbbreviation
FROM STATE_VIEW
where IsBig = 1;

enter image description here

db fiddle link is here.

  • 1
    you rock. Exactly what I was afraid of, but exactly what I needed to know. Thanks! – liver.larson Apr 18 '17 at 22:24
  • the creation of that test data is fantastic, and definitely one area I am lacking. I tried to test similarly, but just didn't have enough data to even get SQL Server to try to use the index. – liver.larson Apr 18 '17 at 22:28
8

The short answer is no.

Let's look at the Users table in Stack Overflow, and get a big 3.

SELECT TOP 3 u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation DESC

This gives us back three IDs: 22656, 29407, 157882. We'll call them our big three.

If we create a view with a tailor made index, the index gets used to satisfy the view.

CREATE VIEW dbo.TopFive
AS 
SELECT TOP 5
        Id, CASE WHEN u.Id IN (22656, 29407, 157882) THEN 1 ELSE 0 END AS IsBig
FROM dbo.Users AS u
ORDER BY u.Reputation DESC
GO 

CREATE NONCLUSTERED INDEX ix_Users_View ON dbo.Users (Reputation DESC, Id)

That's easy to show by querying the view.

SELECT *
from dbo.TopFive AS tf

Nuts

Now if we try to filter on that column, the plan changes.

SELECT *
from dbo.TopFive AS tf
WHERE tf.IsBig = 1

Nuts

Now there's a filter operator that gets added to right before the SELECT. That means the rows aren't filtered when we access the index.

Nuts

If you want to make that SARGable, your best bet would be to add a computed column to materialize the CASE result.

ALTER TABLE dbo.Users 
ADD IsBig AS CONVERT( BIT, CASE WHEN Id IN (22656, 29407, 157882) THEN 1 ELSE 0 END )

Which should be a pretty simple addition to a table of states.

  • the computed column did occur to me as an alternative. In my case, I wonder if going that route would cause different problems in that this is a replicated database for reporting. In any case, we need to get better at keeping track of small enhancements like that which do not exist in the source db, which makes them kind of a maintenance and DR risk. Thanks for the detailed response! – liver.larson Apr 18 '17 at 23:07
5

Unfortunately I have to optimize views like this one all the time. The trick I would use for your query is to divide it in two non overlaping queries and use UNION ALL to merge the results. So the optimizer would have a to choose between one resultset or another if a condition on IsBig is specified. Otherwise it returns everything. Obviously this is harder to mantain.

SELECT
  StateAbbreviation,
  1 AS IsBig
FROM
    tblStates
WHERE
    StateAbbreviation in ('AK' , 'TX')
UNION ALL
SELECT
  StateAbbreviation,
  0 AS IsBig
FROM
    tblStates
WHERE
    StateAbbreviation NOT in ('AK' , 'TX');
  • Interesting pattern. In reality, the state example is an oversimplified version of the problem for the business. In reality, there are lots of fields that need similar treatment, and there are lots of flags that may exist over a single field (say, "red states") - so they would not easily divide into neat discrete buckets. Ultimately, I am looking at different ways to simplify /standardize the report authoring experience, whilst trying to avoid introducing ETL or schema changes because the tables are replicated from an OLTP source, and replication changes would override schema enahancements. – liver.larson Apr 19 '17 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.