title: How to simulate count distinct in an indexed view?
body: <p>I'm working with a database hosted within Microsoft SQL Azure v12.0</p>
  <p>I'm using index advisors to help determine where indexes would really be beneficial, without over-indexing my database.</p>
  <p>I'm running into situations where indexed views would really help eliminate the need to <code>GROUP BY</code> large amounts of data every time someone runs a query.</p>
  <p>I ran into a puzzling error while trying to craft an indexed view for one of my tables.<br />
  This is a pretty common scenario within my database, so I'd like to know if there is an all-around general solution that I can repurpose wherever needed.</p>
  <p>The error I receive looks like this:</p>
  <blockquote>
  <p>Msg 10126, Level 16, State 1, Line 12<br />
  Cannot create index on view &quot;Database.dbo.vwCountDistinctExampleIX&quot; because it uses aggregate &quot;<code>COUNT_BIG</code>&quot; with the <code>DISTINCT</code> keyword.<br />
  ...<br />
  Consider use of a <code>GROUP BY</code> or <code>COUNT_BIG(*)</code> view to simulate <code>DISTINCT</code> on grouping columns.</p>
  </blockquote>
  <p>The issue arises when I need to <code>COUNT_BIG ( DISTINCT {expression} )</code> which is obviously not allowed within indexed views.<br />
  The error message seems to imply that you can simulate <code>COUNT DISTINCT</code> behavior and get the same results <em>without</em> using <code>DISTINCT</code>, which would be really nice since it would allow me to index that column and again help eliminate another type of expensive <code>GROUP BY</code> clause in common queries (since the execution plan can just refer to the index instead of regrouping the table data over and over).</p>
  <p>I don't really understand how <code>COUNT_BIG( * )</code> works in this context, so I don't understand what the error message is trying to suggest.<br />
  Is there a way I could get this aggregate value without using <code>DISTINCT</code> while still being able to index the aggregate column?</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE VIEW vwCountDistinctExampleIX
      WITH SCHEMABINDING
  AS
  
  SELECT
      t1.ID,
      COUNT_BIG( DISTINCT t2.Field2 ) AS DistinctTotal,
      COUNT_BIG( * )                  AS CountStar
  
  FROM
      dbo.Table1 AS t1
      INNER JOIN dbo.Table2 AS t2 ON t2.Field1 = t1.ID
  
  GROUP BY
      t1.ID
  
  GO
  
  /* Error here: Cannot create index on view &quot;Database.dbo.vwCountDistinctExampleIX&quot; because it uses aggregate &quot;COUNT_BIG&quot; with the DISTINCT keyword. */
  CREATE UNIQUE CLUSTERED INDEX [PK vwCountDistinctExampleIX ID]  ON vwCountDistinctExampleIX ( [ID] )
  
  CREATE INDEX [IX vwCountDistinctExampleIX DistinctTotal ON vwCountDistinctExampleIX ( DistinctTotal )
  
  GO
  </code></pre>