SQL Server Indexed View Division

Question

Is it possible to perform a division in the SELECT statement of an indexed view? The moment I add a column result which would be one column's value divided by the other, I start getting the below error:

Cannot create the clustered index 'CX_t' on view 'MyDB.dbo.getUsageForUsers' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

SUM(ISNULL(DATEDIFF(minute, ac.StartTime, ac.EndTime), 0)) AS Effort,
SUM(ISNULL(DATEDIFF(minute, ut.StartTime, ut.EndTime), 0)) AS Availability, 
-- Below line leads to the error in the question
SUM(ISNULL(DATEDIFF(minute, ac.StartTime, ac.EndTime), 0)) / SUM(ISNULL(DATEDIFF(minute, ut.StartTime, ut.EndTime), 0)) AS Utilisation,

Thanks

Answer 1

As the error says, you cannot create expressions that work with the results of aggregates. What you can do though, is to create two views. The first view, the indexed one, just computes the aggregates (Effort and Affordability). The second view, non-indexed, uses the first view and can perform further calculations (Utilisation), and is used by querying code.

You'll still benefit from the indexes maintaining the aggregates, which is the costly operation that indexed views are often introduced to address.

CREATE VIEW dbo.V1
WITH SCHEMABINDING
AS
    SELECT
       SUM(ISNULL(DATEDIFF(minute, ac.StartTime, ac.EndTime), 0)) AS Effort,
       SUM(ISNULL(DATEDIFF(minute, ut.StartTime, ut.EndTime), 0)) AS Availability,
    ...

CREATE INDEX IX_V1 on dbo.V1 (...)

CREATE VIEW dbo.V2
WITH SCHMEBINDING
AS
    SELECT
        Effort,
        Availability,
        Effort/Availability as Utilisation
        /* Other calcs */
    FROM
        dbo.V1 WITH (NOEXPAND)