How to simulate a count other than an indexed view?

Question

I work with a database hosted in Microsoft SQL Azure v12.0

I use index advisors to determine where indexes will be really useful, without re-evaluating my database.

I am faced with situations where indexed views really help eliminate the need for GROUP BY large amounts of data every time someone runs a query.

I ran into a cryptic error trying to create an indexed view for one of my tables.
This is a fairly common scenario in my database, so I would like to know if there is a general solution that I can repackage where necessary.

The resulting error is as follows:

Msg 10126, Level 16, State 1, Line 12
It is not possible to create an index in the view "Database.dbo.vwCountDistinctExampleIX" because it uses the aggregate " COUNT_BIG " with the keyword DISTINCT .
...
Consider using the GROUP BY or COUNT_BIG(*) to model DISTINCT when grouping columns.

The problem occurs when I need COUNT_BIG ( DISTINCT {expression} ) , which is clearly not allowed in indexed views.
The error message seems to imply that you can simulate the behavior of COUNT DISTINCT and get the same results without using DISTINCT , which would be very nice, as it would allow me to index this column and again help eliminate another type of expensive GROUP BY in general queries (because the execution plan can simply refer to the index, and not to rearrange the table data again and again).

I really don't understand how COUNT_BIG ( * ) works in this context, so I don't understand what the error message is trying to suggest.
Is there a way to get this cumulative value without using DISTINCT while you can still index an aggregate column?

 CREATE VIEW [vwCountDistinctExampleIX] WITH SCHEMABINDING AS SELECT [T1].[ID] , COUNT_BIG ( DISTINCT [T2].[Field2] ) AS [DistinctTotal] , COUNT_BIG ( * ) AS [CountStar] FROM [dbo].[Table1] [T1] JOIN [dbo].[Table2] [T2] ON [T2].[Field1] = [T1].[ID] GROUP BY [T1].[ID] GO CREATE UNIQUE CLUSTERED INDEX [PK vwCountDistinctExampleIX ID] -- Error here ON [vwCountDistinctExampleIX] ( [ID] ) CREATE INDEX [IX vwCountDistinctExampleIX DistinctTotal] ON [vwCountDistinctExampleIX] ( [DistinctTotal] ) GO 

Answer 1

I don’t think you can achieve what you want right in the indexed view. However, if the number of rows in table 2 is large, but the number of different values ​​in field 2 is not so large, the following approach may help:

 CREATE TABLE Table1 (ID INT PRIMARY KEY) CREATE TABLE Table2 (Field1 INT NOT NULL REFERENCES Table1, Field2 INT NOT NULL) INSERT INTO Table1 VALUES (1),(2),(3) INSERT INTO Table2 VALUES (1,10),(1,20),(1,20),(2,30) GO CREATE VIEW dbo.IndexedView1 WITH SCHEMABINDING AS SELECT ID, Field2, COUNT_BIG(*) AS Cnt FROM dbo.Table1 T1 INNER JOIN dbo.Table2 T2 ON T1.ID = T2.Field1 GROUP BY ID, Field2 GO CREATE UNIQUE CLUSTERED INDEX PK_IndexedView1 ON IndexedView1 (ID, Field2) GO CREATE VIEW SimpleView2 AS SELECT ID, COUNT_BIG(*) AS DistinctTotal, SUM(Cnt) AS CountStar FROM dbo.IndexedView1 X GROUP BY ID 

Answer 2

You can partially index this query by grouping both columns:

 GROUP BY [T1].[ID], [T2].[Field2] 

The query processor is smart enough to now use the index to compute individual ones.

Clearly this view is less effective than you might want, but it is better than nothing.