none
Does SQL Server 2014 support MEMORY_OPTIMIZED Indexed Views?

    Question

  • 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
    Saturday, November 02, 2013 5:37 PM

All replies

  • Hello Sandro,

    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
    Sunday, November 03, 2013 12:08 PM
  • 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 2012


    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?

    thanks


    Sunil Agarwal, SQL Server PM

    Monday, November 04, 2013 6:11 AM