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: http://msdn.microsoft.com/en-us/library/ms143761(SQL.90).aspx 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: http://msdn.microsoft.com/en-us/library/cc645993.aspx Disappointed! Just wait for my Scrooge List 2010… Later Brian
Common misconceptions with Indexed Views
Try these samples to learn Business Intelligence...Next Post
TDE vs Column Encryption
Cisco fears competition to its Hyperflex hyperconverged platform and kicks Nutanix out of it's Solution...
A review of 19 companies that offer free cloud storage
By forcing Windows 10 on users, Microsoft has lost the tenuous trust and credibility users had in the...
The FBI has reportedly found evidence that foreign hackers breached two state election databases in...
Most security tools are focused on keeping external attackers at bay. But what about the sensitive data...
As the number of ransomware demands increase, users should be aware of hollow threats.
Four innovation leaders share their adventures in scaling an analytics infrastructure to back up...