Microsoft Subnet An independent Microsoft community View more

Indexed Views are a good feature but in which Edition?

Common misconceptions with Indexed Views

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”. Source: 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 are 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: Disappointed! Just wait for my Scrooge List 2010… Later Brian

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.
Must read: Hidden Cause of Slow Internet and how to fix it
Notice to our Readers
We're now using social media to take your comments and feedback. Learn more about this here.