SQL Server 2016 brings a host of new features and enhancements, and one area that has caught my attention is the InMemory OLTP capabilities. In this article, we will take a closer look at some of the key enhancements in SQL Server 2016 that make InMemory OLTP even more powerful and flexible.

One of the major improvements in SQL Server 2016 is the support for LOB (Large Object) data types in memory-optimized tables and natively compiled stored procedures. This means that you can now store and access LOB data types such as varchar(max), nvarchar(max), and varbinary(max) in memory-optimized tables. In previous versions of SQL Server, the lack of support for LOB data types wider than 8060 bytes limited the flexibility in designing database schemas and often required additional complexity such as data partitioning or external storage for LOB data.

Let’s take a look at a simple script to see how this works:

CREATE TABLE dbo.SQLAuth_InMemory_OLTP_DataTable
(
      SQLAuth_Id   INT IDENTITY PRIMARY KEY NONCLUSTERED,
      SQLAuth_NVarCol NVARCHAR(3000),
      SQLAuth_VarCol VARCHAR(2000),
      SQLAuth_VarBinCol VARBINARY(2000),
      SQLAuth_LobCol NVARCHAR(MAX)
) WITH (MEMORY_OPTIMIZED = ON);

In previous versions of SQL Server, executing this query would have resulted in an error. However, in SQL Server 2016, this query runs without any issues, allowing us to store records with a length greater than 8060 bytes in memory-optimized tables.

With these enhancements, I believe it’s time to reconsider using InMemory OLTP capabilities in SQL Server 2016. I would love to hear from you about your experiences with InMemory OLTP and the reasons why you may have evaluated it in the past but decided not to use it due to missing features. Please share your thoughts and experiences in the comments section below.