• Jeff Moden - Saturday, January 12, 2019 7:13 AM

    The only thing that I don't agree with is the part where a function may automatically pick to use an indexed view instead of the tables that have been cited in the code.  I've heard of that "feature" before (and the DTA even has such an option built into it) and I suppose that optimizer could chose to use an index from a materialized view, but I've never seen anyone be able to prove it, especially where the view provides materialized aggregation.  Do you have an example or a URL that has such an example handy?

    No, but it does on my desktop, which is running SQL 2016.   I changed my text to say a function may use it because at first I thought an ITVF would have to reference the view explicitly.  I guess it shouldn't be surprising:  an indexed view is just another table structure.  I'll test it at work next week on 2012 and get back to you.

    One thing to be clear on.   All of our indexed views involve multiple tables.   They aren't aggregations.   We pay the price to avoid some expensive joins in big searches but fortunately the rows in the tables involved are fairly static once they're inserted.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills