Indexed Views are Great — For Some…

There has been a lot of confusion about Indexed Views in SQL Server. This is a great feature for Data Warehouses that captures complex number crunching into persisted storage. Unlike standard views they can provide significant performance gains. Oracle has a similar feature called Materialized Views. Indexed Views were introduced in SQL Server 2000 as an Enterprise Edition feature.

The confusion arose in SQL Server 2005 when it appeared that all editions supported Indexed Views. I remember seeing Books Online listing Indexed Views as a feature across the board. However, this meant that, yes, we could create Indexed Views in all editions but the Query Optimizer would only use it if you were using the Enterprise or Developer editions. There was a loop-hole though. With the minor editions you could force the usage of the Index Views using hints:  WITH(INDEX(view index name)) and the query option NOEXPAND.

Now Books Online 2005 has been updated to specify exactly what happens using a subtext item:

“This feature is supported in all editions of SQL Server 2005, except that indexed view-to-query matching by the Query Optimizer is supported only in Enterprise Edition and Developer Edition. Indexed views can be created in all editions of SQL Server 2005, and queried by name using the NOEXPAND hint”.

The view-to-query feature is pretty cool. It means that even if you do not use the view by name, the query optimizer will pick up on the Indexed View if you are doing the same kind of calculations with the base table. This is quite sophisticated and is rightly an Enterprise level feature.

However, in the other editions even if you access the view directly, the query optimizer would ignore the Index on the view. Thankfully, we had our loophole using index hints and NOEXPAND.

But wait, I just tried the loophole in SQL Server 2008 Standard edition and it even ignores the hint. Dang! So Microsoft is not only releasing new features mainly to the Enterprise edition (and now Datacenter edition in R2),  it is busy closing loopholes to entice us to upgrade editions. My fears are confirmed in the following MSDN document:

Features Supported by the Editions of SQL Server 2008 R2:

http://msdn.microsoft.com/en-us/library/cc645993.aspx

Disappointed! Just wait for my Scrooge List 2010…

Author: Brian Egler

Related Courses:

SQL Server 2008 for Administration (M6231, M6232)

Writing Queries Using Microsoft SQL Server 2008 Transact-SQL (M2778)

Maintaining, Troubleshooting, and Developing Solutions with Microsoft SQL Server 2008 Reporting Services (M6236)

Implementing and Maintaining SQL Server 2008 Analysis Services and Business Intelligence Solutions (M6234)

In this article

Join the Conversation

2 comments

  1. Simon Sabin Reply

    Would love for a repro.

    I’ve just tried this on sqlexpress and it works as expected.

    CREATE VIEW vContactOrder
    WITH SCHEMABINDING
    AS
    SELECT c.CustomerID , o.OrderID
    FROM dbo.Customers as c
    JOIN dbo.[Orders] as o
    ON c.CustomerID = o.CustomerID
    where c.City = ‘London’;
    GO

    CREATE UNIQUE CLUSTERED INDEX ivContactOrder ON vContactOrder
    (CustomerId, OrderID);

    select *
    from vContactOrder with (noexpand)

    1. Brian Egler Reply

      yes, it works, but if you look at the execution plan it shows that it does not use the indexed view at all…it’s as if the index does not exist…
      Brian