Released in 2014, SQL Server's memory-optimized tables offers significant performance advantages over traditional tables including lock-free writes, fully compiled stored procedures, and the option to avoid disk I/O altogether. But it also came with a lot of limitations, including the inability to work with the large documents favored by NoSQL style designs.
With SQL Server 2016, many of those limitations have been removed. First up is support for LOB types in both memory-optimized tables and compiled stored procedures. This means that you can use varChar(max), nVarChar(max) (both of which cover XML and JSON data) and varBinary(max). The 8060 byte row size limit has also been lifted, even for wide tables that don't contain LOB types.
That said, Microsoft is recommending against using this feature if possible. If you can fit everything into a varChar(8000) or smaller column instead of varChar(max), you won't have to pay for hitting the hidden table that stores large objects during writes.
Constraints for Memory-Optimized Tables
Another limitation of memory-optimized tables was the inability to create constraints (aside from unique primary keys). While not strictly necessary from an application design standpoint, constraints do eliminate the possibility for several types of data corruption.
- FOREIGN KEY constraints between memory-optimized tables
- CHECK constraints
- UNIQUE constraints
Note that foreign key constraints between a normal table and a memory-optimized table are still not permitted.
Natively Compiled Stored Procedure Improvements
If you are unfamiliar with the term, a "compiled stored procedure" is compiled into highly optimized machine code when created. It can only operate against memory-optimized tables, but offers significant performance improvements compared to normal stored procedures, which are interpreted at runtime.
In addition to support for LOB types, you can now use OUTPUT clauses with INSERT, UPDATE, and DELETE statements. This can eliminate the need for a separate query, which in turn may eliminate the need for a transaction and the associated locking.
Other standard SQL features now available in compiled stored procedures include:
- UNION and UNION ALL
- SELECT DISTINCT
- OUTER JOIN
- Subqueries in SELECT statements (EXISTS, IN, scalar subqueries)
Natively Compiled Functions
You can now natively compile scalar functions. To do so, you need to use WITH NATIVE_COMPILATION, SCHEMABINDING as a directive and wrap the actual code inside a BEGIN ATOMIC block. This differs from compiled stored procedures, which are marked with only the WITH SCHEMABINDING directive.
Natively Compiled Triggers
Continuing the theme, AFTER triggers can now be placed on memory-optimized tables if WITH NATIVE_COMPILATION is used.
For more information see What's new in SQL2016 CTP3 and What’s new for In-Memory OLTP in SQL Server 2016 since CTP3.
