Connect Digest : 2011-06-27

Sorry I have fallen off the Connect Digest wagon for the past few weeks; been a little swamped since returning from SQLCruise Alaska. Not sure I'll be able to assemble a digest every week, but I'll certainly try to keep a steady pace.

This week I wanted to highlight a few suggestions around indexed views. With the coming of SQL Server code-named "Denali" we will be pushed toward the new columnstore index as an alternative to indexed views. But this won't be for all cases, and it likely won't be available to all editions, either. So I hope that these requests don't start to get discarded with the simple workaround of upgrading to Denali, possibly upgrading to Enterprise Edition, and converting to the read-only columnstore index.


Ralf Dietrich suggests relaxing some of the restrictions on indexed view creation; for example, the fact that an index can't be created on a view with non-deterministic functions. He doesn't want it to just suddenly start working, but to require a hint to make work.

#577305 : indexed view improvements


Razvan Socol has a similar suggestion, that views with one or more unions could be indexed. His specific use case is not just for performance but also that he'd like to define a foreign key against the view.

#125968 : Indexes on views that contain UNION-s


xor88 is asking for stacked (or "nested") indexed views. Essentially he wants to be able to create an index on a view that references another indexed view, in order to support persist aggregations over existing persisted aggregations.

#641364 : stacked indexed views


Back in 2007, I asked for more aggregation support in indexed views – in addition to counts, for example, it would be helpful to have min/max support. More work for the engine, of course, but it could dramatically

#267516 : Expand aggregate support in indexed views (MIN/MAX)


Adam Machanic (@AdamMachanic) filed this bug, concerning the fact that indexed view creation takes a schema modification lock on the base table (resulting in blocking both readers and writers), when it would be possible to take only a schema stability and shared table lock (blocking only writers).

#581056 : Indexed view creation causes queries on the base table to block


A comment on the above item from Paul White (@SQL_Kiwi) made me think of this suggestion, to delay index creation so that it could be done in the background and have less impact on real-time operations:

#593032 : Indexed Views – Option for Delayed Seperate Index creation Process – Akin to Full Text Indexing


That's it for this week's digest. Please let me know if you have suggestions for future focus areas!


Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)