title: Indexing views with a CTE
body: <p>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 <code>alter</code> the query to add <code>with schemabinding</code> 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 <code>WITH SCHEMABINDING</code>s main purpose was to allow you to index a view</p>
  
  <p>new and improved with more query action</p>
  
  <pre><code>;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
  </code></pre>
  
  <p>And the code to create the index</p>
  
  <pre><code>create unique clustered index [TestIndexName] on [dbo].[MyView]
  ( pat_id asc, drug_class asc, counts asc)
  </code></pre>