SQL SERVER 2016 – InMemory OLTP LOB Datatype Enhancement

My exploration with SQL Server 2016 features are just continuing and I am delighted every other day to figure out something new been added to the capabilities which in the opinion were of limited capacity in the prior versions of SQL Server. I will spare the ranting exercise for now but will look at some of the things that caught my attention in my recent exploration with SQL Server InMemory OLTP enhancements.

SQL Server 2016 brings support for LOB data types to memory-optimized data and natively compiled stored procedures; this includes support for these types in memory optimized tables, access to these columns through both inter-op and natively compiled procedures, the declaration and use of LOB variables and parameters in natively compiled procedures, and support for LOB expressions in natively compiled procedures. As part of the LOB supportability, it is now permitted to store records with length >8060 bytes in memory-optimized tables.

In SQL Server 2014 – the lack of support for memory-optimized table records wider than 8060 bytes reduces the flexibility for customers in designing their DB schema and requires additional complexity in the likes of data partitioning (horizontally or vertically) or external storage for LOB data. This did the slow adoption of this technology in general.

LOB data types supported in SQL Server 2016 include:

• varchar(max)
• nvarchar(max)
• varbinary(max)

Let me take a simple script to show you 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);
GO

The same query would have caused an error in SQL Server 2014 which in this version of SQL Server just runs fine.

With a number of such capabilities added, I personally think we need to relook at using InMemory OLTP capabilities inside SQL Server 2016. Do let me know what were some of the other reasons you evaluated InMemory and dropped using the same because it didn’t have a feature? Use the comments sections to share your experience.

Reference: Pinal Dave (https://blog.sqlauthority.com)