Fellow MVP Paul White recently blogged about a SQL Server bug. His blog post is called An Indexed View Bug with Scalar Aggregates
It’s a really well-written article (as always). After reading it, it’s natural to wonder whether your databases have any such indexed views?
Chances are that you don’t have any. These indexed views aren’t too common, but if you do have indexed views that use scalar aggregates, here’s a query that can help you find them.
The following query finds indexed views without
GROUP BY that have exactly one row in any partition.
with IndexedViewIds as ( SELECT [object_id] from sys.indexes INTERSECT SELECT [object_id] from sys.views ), IndexedViewInfo as ( SELECT [object_id], OBJECT_SCHEMA_NAME([object_id]) as SchemaName, OBJECT_NAME([object_id]) as ViewName, OBJECT_DEFINITION([object_id]) as [Definition] FROM IndexedViewIds ) SELECT v.[object_id], v.SchemaName, v.ViewName, v.[Definition] FROM IndexedViewInfo v WHERE NOT EXISTS ( SELECT * FROM sys.partitions WHERE [object_id] = v.[object_id] AND [rows] <> 1 ) AND v.[definition] NOT LIKE '%GROUP BY%'
The query isn’t perfect. It’s actually possible (but rare) to get false positives here. This query doesn’t look for any aggregate keywords. So look for them in the
SELECT list. Also make sure no
GROUP BY clause exists.
It’s also possible (but rare) to miss some indexed views when
GROUP BY gets mentioned, but not used. For example, if an indexed view definition contains the phrase
GROUP BY in a comment, it won’t show up in this list.
(For my curious co-workers, none of our indexed views use scalar aggregates)