Sunday, March 18, 2018

In Memory - Memory optimized tables in SQL Server 2014 and SQL Server 2016

One of the biggest feature enhancement for SQL Server 2014 is In Memory Table.
Which is a different table structure compared to traditional 8 KB/Page structure .This is the one of the limitation of  SQL Server that cant process more requests whether there is more memory or faster disk available.

Design concerns (SQL Server 2014 ) 

Table construct
No LOB data types
No Constraints support (Only Primary Key
No Identity or calculated column or CLR
Data and Table Size
Maximum size Schema and Data :512 GB

SQL Server 2016 Enhancements on in Memory OLTP
Supports Full schema changes ADD/ALTER/DROP  for Column and constraints
MARS Support
ADD/DROP index Support
MERGE Operation
Improvements to DMVs sys.dm_db_xtp_checkpoint_stats ,sys.dm_db_xtp_checkpoint_files 
Support for Transparent Data Encryption.
Operational Analytics - Columnstore index on in memory table

Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables.

Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure. In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile memory-optimized tables

Other design enhancement in SQL Server 2016 Management studio for Memory optimized table
Memory Optimization Advisor
Native compilation advisor

In-Memory OLTP provides full durability for memory-optimized tables. When a transaction happens to an in memory table SQL Server guarantees that the changes are permanent (will survive a database restart), provided the underlying storage is available.

When to you use In Memory table ?
increase transnational throughput
You want to improve data ingestion
Reduce latency
Remove latency spike
If you want to store data for staging state


  1. This comment has been removed by the author.

  2. Thank you so much for sharing such an interesting and useful post.I think REST API can also provide more useful and adequate information about this.
    SQL Server Load Rest API

  3. Nice article I was impressed by seeing this blog, it was very interesting and it is Thanks for sharing all the information with us all.very useful for me.
    oracle training in chennai

    oracle training institute in chennai

    oracle training in bangalore

    oracle training in hyderabad

    oracle training

    hadoop training in chennai

    hadoop training in bangalore

  4. This is a very big and very important subject that you have shared with us, thank you very much for that.

    website development company in Surat Gujarat

  5. Microsoft SQL Server 2019 Standard provides all database features with analytics and reporting capabilities. It also includes basic availability features and disaster recovery. Microsoft SQL Server Standard can build rich content management applications.

  6. Highly energetic blog, like it. Keep up the great work. Read about sql server online training from Maria Academy.