CREATE BLOG dbo.SqlServer: Cannot Create Index on View with MAX, MIN or AVG

Thursday, November 02, 2006

Cannot Create Index on View with MAX, MIN or AVG

Last month I was doing a few SQL-based optimizations for the database behind our sites and I ran into a strange restriction that I can find no logic behind. Here is the error message:

Cannot create index on view "dbo.MyView" because it uses aggregate "MAX". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

The source of the view was something like this:

CREATE VIEW MyView
WITH SCHEMABINDING
AS
SELECT IdColumn, COUNT_BIG(*) AS NumberOfSales
, ISNULL ( MAX(wv.DateAdded), GETDATE() ) AS DateOfLastSale
, ISNULL ( SUM ( v.SalesPrice ) ) 0 )
AS TotalPrices
FROM dbo.StoreSales v JOIN dbo.WebsiteSales wv
ON v.SaleId = wv.SaleId
WHERE wv.IsEnabled = 1
GROUP BY StoreId
GO
CREATE UNIQUE CLUSTERED INDEX ci_MyView
ON MyView(StoreId)
GO

I have scoured the documentation and I can see that MAX, MIN and AVG (among other things) are not allowed but I can find no mention of *why* they aren't allowed. There's a chapter in the SQL Server 2000 Resource Kit (here) that talks about the restrictions but again, it makes no mention of the "why". Maybe Kalen's new book, Inside SQL Server 2005: The Storage Engine, can offer something but I'm not holding my breath.

Anyone know the logic behind this?