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
Fixed ; No ALTER TABLE
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
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
Fixed ; No ALTER TABLE
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
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
