BT

SQL Server Now Offers NoSQL Style Memory-Optimized Tables

by Jonathan Allen on Mar 29, 2016 |

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.

Rate this Article

Relevance
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss
General Feedback
Bugs
Advertising
Editorial
Marketing
InfoQ.com and all content copyright © 2006-2016 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT

We notice you’re using an ad blocker

We understand why you use ad blockers. However to keep InfoQ free we need your support. InfoQ will not provide your data to third parties without individual opt-in consent. We only work with advertisers relevant to our readers. Please consider whitelisting us.