Post reply

Indexed Views in Standart Edition

  • June 8, 2010 at 1:48 pm

    #387102

    I have an indexed view and I'm not using SQL Server Enterprise edition. So I use the NOEXPAND hint as stated below:

    "Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used".

    But something that I haven't been able to do is to take advantage of the Indexed View without explicitly referencing it:

    "A query does not have to explicitly reference an indexed view in the FROM clause for the query optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the query optimizer estimates that using the indexed view provides the lowest cost access mechanism, the query optimizer chooses the indexed view"

    Both statements are at http://technet.microsoft.com/en-us/library/ms181151.aspx

    I know that if I were using SQL Server Enterprise edition, and if I didn't wanted to use the indexed view in queries that don't explicitly reference it, I would have to add OPTION (EXPAND VIEWS).

    Does anybody know if I can make Non-Enterprise SQL Server take advantage of an existing indexed view without explicitly referencing it?

    I'm using SQL Server 2005 Standard Edition

    Thanks a lot,

    Luiz

  • June 10, 2010 at 9:00 am

    #1178664

    I am not sure but I think the answer is no.

    I read this in the 70-433 exam prep book

    In SQL Server Enterprise Edition, query substitution goes one step further when an indexed view is present. Normally, the optimizer selects indexes created against tables referenced within a query if it determines that a given index improves query performance. In SQL Server Enterprise Edition, if the optimizer determines that the data can be retrieved more efficiently through the indexed view, it then builds a query plan that ignores the base tables referenced by the query and instead retrieves data from the indexed view instead of the tables.

    So sounds like unless you are using Enterprise Edition, the optimizer does not automatically consider the indexed view when only tables are referenced.

    Also here http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server-Page-4.htm

  • June 10, 2010 at 9:21 am

    #1178672

    Thanks Ray

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply