Expand aggregate support in indexed views (MIN/MAX) - by AaronBertrand

Status : 

 


64
0
Sign in
to vote
ID 267516 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 4/3/2007 12:00:38 PM
Access Restriction Public

Description

I often think that my reporting queries would benefit from indexed views 
where the system would maintain for me the element along with the min or max 
of some column.  For example, a lot of the reports we run ask, "when was the 
last time x happened?"  Unless the clustered index is on x, eventdate desc, 
this query can be a very expensive scan over 30MM or 300MM rows.

I understand now that the overhead here to maintain the index is much more expensive than simply incrementing / decrementing a count.  Not only do we have to compare new / changed values to the existing min / max in the view, we also have to find the "next" highest or lowest value when the row associated with the current value is deleted or changed.  However, if given the ability, I would like the option to create such a view and compare index maintenance overhead with query performance.

If implemented, I can see that it would only make sense for certain datatypes, e.g. datetime and numeric types would be allowed, but I don't see why NVARCHAR(MAX) or XML or IMAGE would have to be.  I'd also be fine with a limitation of one such aggregate per view.

2007-04-18

And as Adam suggests, you should properly implement windowing functions, e.g. in a view with the following query:

SELECT
    Col1,
    Col2,
    Col3,
    TheRow = ROW_NUMBER() OVER (PARTITION BY Col1, ORDER BY Col2 DESC)
FROM Tbl

Let me create an index on, for example, (Col1, theRow) ... have it implemented properly and make sure the optimizer can handle all scenarios.  This index was allowed in RTM but, as Stefano explained, it was an oversight that was fixed in the SP1 timeframe.
Sign in to post a comment.
Posted by jswartsel on 10/16/2012 at 9:49 AM
I'll echo my support for this one. Being able to put more advanced query constructs into indexed views would be greatly appreciated. Does anyone at Microsoft have an update on this?
Posted by Paul White NZ on 5/25/2012 at 2:58 PM
Adding MAX (and MIN) to indexed views would be tremendously useful in a enormous number of scenarios where we need to optimize finding the most recent record, for example. Trusted indexed views would not be at all useful in most scenarios, since applications that need to do this usually deal with changing data.

I understand the reason for not implementing MIN and MAX is the possibility of having to scan all existing data when maintaining the indexed view after a DELETE. This issue does not arise if there is a suitable index on the underlying tables to support the aggregate (only one row needs to be read using an ordered partial scan). Add a requirement that indexing a view containing MIN or MAX requires such an index, and we're done. You would have to prevent the index being dropped while the indexed view is still schema bound, but that doesn't seem intuitively too difficult.
Posted by Miroslav Sekera on 9/12/2010 at 3:30 AM
Hi Torsten,

I would appreciate the "trusted indexed views" feature too...
would that be available for the Azure SQL too?

Regards,
Miroslav Sekera
developing http://www.glueo.com/
Posted by Torsten [MSFT] on 10/25/2007 at 4:41 PM
Thanks very much for your comments on supporting min/max aggregates for indexed views in SQL Server. We were actually considering what we called trusted indexed views for SQL Server 2008. Those are views that you could create and that would be matched automatically by the query optimizer. However, they would become invalid as a potentially relevant update comes along. You would need to maintain them separately. But, they would allow for much richer T-SQL constructs than regular indexed views. However, we did not have enough resources to put them into the upcoming 2008 release.

However, I'd be curious to learn more about the scenarios behind your motivation to ask for this feature. Some of it I can take from your comment in Connect. But, it would be great if you could explain one level more. I'd also be curious to see how the notion of the trusted indexed view that I described above resonates with what you have in mind.

Thanks and best regards,
Torsten Grabs

Program Manager
SQL Server Query Processor
Posted by Noeld1 on 10/4/2007 at 10:33 AM
If I had $1 for how many times I needed this feature, I would be rich! :D