Indexing views with a CTE

Question

So, I just found out that SQL Server 2008 doesn't let you index a view with a CTE in the definition, but it allows you to alter the query to add with schemabinding in the view definition. Is there a good reason for this? Does it make sense for some reason I am unaware of? I was under the impression that WITH SCHEMABINDINGs main purpose was to allow you to index a view

new and improved with more query action

;with x
as
(
    select   rx.pat_id
            ,rx.drug_class
            ,count(*) as counts
            from rx
            group by rx.pat_id,rx.drug_class

)
select   x.pat_id
        ,x.drug_class
        ,x.counts
        ,SUM(c.std_cost) as [Healthcare Costs]
    from x
    inner join claims as c
    on claims.pat_id=x.pat_id
    group by x.pat_id,x.drug_class,x.counts

And the code to create the index

create unique clustered index [TestIndexName] on [dbo].[MyView]
( pat_id asc, drug_class asc, counts asc)

Solution

1. You can't index a view with a CTE. Even though the view can have SCHEMABINDING. Think of it this way. In order to index a view, it must meet two conditions (and many others): (a) that it has been created WITH SCHEMABINDING and (b) that it does not contain a CTE. In order to schemabind a view, it does not need to meet the condition that it does not contain a CTE.

2. I'm not convinced there is a scenario where a view has a CTE and will benefit from being indexed. This is peripheral to your actual question, but my instinct is that you are trying to index this view to magically make it faster. An indexed view isn't necessarily going to be any faster than a query against the base tables - there are restrictions for a reason, and there are only particular use cases where they make sense. Please be careful to not just blindly index all of your views as a magic "go faster" button. Also remember that an indexed view requires maintenance. So it will increase the cost of any and all DML operations in your workload that affect the base table(s).

3. Schemabinding is not just for indexing views. It can also be used on things like UDFs to help persuade determinism, can be used on views and functions to prevent changes to the underlying schema, and in some cases it can improve performance (for example, when a UDF is not schema-bound, the optimizer may have to create a table spool to handle any underlying DDL changes). So please don't think that it is weird that you can schema-bind a view but you can't index it. Indexing a view requires it, but the relationship is not mutual.

---

For your specific scenario, I recommend this:

CREATE VIEW dbo.PatClassCounts
WITH SCHEMABINDING
AS
  SELECT pat_id, drug_class, 
      COUNT_BIG(*) AS counts
    FROM dbo.rx
    GROUP BY pat_id, drug_class;
GO
CREATE UNIQUE CLUSTERED INDEX ON dbo.PatClassCounts(pat_id, drug_class);
GO
CREATE VIEW dbo.ClaimSums
WITH SCHEMABINDING
AS
  SELECT pat_id, 
    SUM(c.std_cost) AS [Healthcare Costs], 
    COUNT_BIG(*) AS counts
  FROM dbo.claims
  GROUP BY pat_id;
GO
CREATE UNIQUE CLUSTERED INDEX ON dbo.ClaimSums(pat_id);
GO

Now you can create a non-indexed view that just does a join between these two indexed views, and it will utilize the indexes (you may have to use NOEXPAND on a lower edition, not sure):

CREATE VIEW dbo.OriginalViewName
WITH SCHEMABINDING
AS
    SELECT p.pat_id, p.drug_class, p.counts, c.[Healthcare Costs]
      FROM dbo.PatClassCounts AS p
      INNER JOIN dbo.ClaimSums AS c
      ON p.pat_id = c.pat_id;
GO

Now, this all assumes that it is worthwhile to pre-aggregate this information - if you run this query infrequently, but the data is modified a lot, it may be better to NOT create indexed views.

Also note that the SUM(std_cost) from the ClaimSums view will be the same for every pat_id + drug_class combination, since it's only aggregated to pat_id. I guess there might be a drug_class in the claims table that should be part of the join criteria too, but I'm not sure. If that is the case, I think this could be collapsed to a single indexed view.

Comments

[RandomUs1r]

Any view that pulls cross server data will fail to index, and that makes perfect sense because you can't index what's not always there. I wonder if OP has some rpc calls, no code was posted so w/e.

[Adir D]

@RandomUs1r I don't think that's the scenario, since the OP was able to alter the view and add SCHEMABINDING. If it referenced three- or four-part names, this shouldn't be possible.

[wootscootinboogie]

When I tried to use a CTE in a view I wanted to schemabind I got the error Cannot create index on MyDatabase because it references a CTE. Views referencing CTEs cannot be indexed Level 16...whatever Level 16 means. I'm using SSMs

[wootscootinboogie]

@AaronBertrand you were right in your intuition that I was trying to make it faster. Plus learn some, I'd never tried indexing a view and just wanted to get my hands dirty with it.

[Adir D]

You should start by reading things like Designing Indexed Views instead of just trying to add an index to some view you have lying around and hope that it makes it faster. They are typically used for pre-aggregating things like COUNT() and SUM(), not just to make queries against a view faster.

[wootscootinboogie]

In retrospect I suppose I should have added the query on which the view was based, but it does involve aggregating tens of millions of rows on a few columns.

[wootscootinboogie]

Msg 10137, Level 16, State 1, Line 1 is the proper error code received when I tried to add an index to a view with a CTE

[Adir D]

Ok, so maybe it is a good candidate for an indexed view. Still impossible for me to tell. But you should understand the scenario and come to the fact that an indexed view might be good for the right reasons, instead of waving an indexed view around as a solution looking for a problem. :-)

[Adir D]

You can't index a view with a CTE. This is correct. I misunderstood the explanation in your question - based on the title, I thought you were getting an error trying to create a view WITH SCHEMABINDING.

[wootscootinboogie]

@AaronBertrand +1 for schemabinding lesson