locked
aggregation in indexed views RRS feed

  • Question

  • Hi,

                I was trying to learn about the indexed view by creating a simple index view on a query(select count_big(*) cc from emp).

    When i checked the execution plan by selecting from indexed view, it is showing like, table scan(100%)->stream aggregation->select.Here what is the reason for aggregation in between?In indexed view,aggregation should be precalculated right?

    Monday, July 9, 2012 4:28 PM

Answers

  • Which edition of SQL Server are you using? If you are using Standard Edition try adding the hint, i.e...

    Select cc
    From <ViewName> With (NoExpand)

    i.e....

    http://arnosoftwaredev.blogspot.com.au/2010/09/sqlserver-standard-edition-does-support.html

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem


    • Edited by moort Tuesday, July 10, 2012 7:48 AM
    • Marked as answer by NikkRED Tuesday, July 10, 2012 2:07 PM
    Tuesday, July 10, 2012 7:35 AM
  • I am using Enterprice Evaluation version 2012.Any way it is working when i use 'with (NoExpand)' on the select statment.Thanks...
    
    
    • Marked as answer by NikkRED Tuesday, July 10, 2012 2:06 PM
    Tuesday, July 10, 2012 2:06 PM

All replies