Hi, we're excited about the upside performance with moving our systems to SqlServer 2014. One question my team has involves our current use of indexed views for our high read versus write behavior. Will you elaborate on whether indexed views are supported in SqlServer 2014 and whether there is a mechanism to also force them be memory optimized with the MEMORY_OPTIMIZED option that exists with tables? If memory optimized indexed views are not supported, will you recommend an approach that we should consider instead?
- Moved by Kalman Toth Monday, November 04, 2013 12:06 AM Not db design
The "MEMORY_OPTIMIZED" option is available for tables and indexes, but not for views; see Memory-Optimized Tables and Indexes
Memory optimization is for "physical" data while a view is just a definition how to return those data, therefore a memory optimization can't apply to a view. But of course you can create views on memory optimized tables.
Olaf Helper[ Blog] [ Xing] [ MVP]
- Proposed as answer by Kalman Toth Monday, November 04, 2013 12:05 AM
Correct, the indexed views are not supported on memory optimized tables.
Kalman>> If memory optimized indexed views are not supported, will you recommend an approach that we should consider instead
sunil>> Not sure how complex your indexed view is? It may be worth running the query without the indexed view on the memory-optimized table. Also, if you can share some information on what are the bottleneck on the table as you are considering moving it to memory-optimized. For example, are you running into latch waits?
Sunil Agarwal, SQL Server PM