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?
Saturday, November 02, 2013 5:37 PM
- 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]
Sunday, November 03, 2013 12:08 PM
- Proposed as answer by Kalman Toth Monday, November 04, 2013 12:05 AM
I am moving it to a more appropriate forum.
Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012Monday, 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 PMMonday, November 04, 2013 6:11 AM