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.
====================
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.
====================
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.
====================
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:
====================
That's it for this week's digest. Please let me know if you have suggestions for future focus areas!
