Top 2 LOBs Enhancements with Memory Optimized and Native Compilations

SQL Server 2014 was the first release of SQL Server with the In-Memory OLTP feature. This feature allows us to create a table in the database that would reside in the memory. Even though it is a memory resident table, it would comply with ACID properties of a transaction and be guaranteed to provide a “no-data-loss” scenario. There were two ways to access In-Memory tables via T-SQL: either use an adhoc query called “interpreted method”, or use a stored procedure called “natively compiled”. As per Microsoft marketing sessions, this can create a performance boost of up to 30 times if In-Memory tables are accessed using a natively compiled stored procedure, but the codeword used is Heckaton (100 times faster). We all know how marketing works and I took a conservative 30 times as acceptable.

There were many limitations when this feature was first release in SQL Server 2014. This was one of the most marked and incompletely cooked features and was called as version 1 (v1) of In-Memory OLTP. Here is quick list of a few of the limitations in SQL Server 2014:

• High-throughput limitations
  • No DML triggers
  • No XML and no CLR data types
• Column limitations
  • Rows are at most 8060 bytes – no off row data
  • No Large Object (LOB) types like varchar(max)
• Scoping limitations
  • No FOREIGN KEY and no CHECK constraints
  • No IDENTITY
  • No schema changes (ALTER TABLE) – need to drop/recreate table
  • No add/remove index – need to drop/recreate table
  • A maximum of 8 indexes, including the index supporting the PRIMARY KEY

There are many limitations which are being lifted by SQL Server 2016. This blog will demonstrate two of the most wanted features: LOBs and Native Compilation.

LOBs enhancement

LOB stands of Large Objects which can be defined in SQL Server which is used to store binary data, image data etc. There are various data types which support large objects such as TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY etc.

If we try to create the below In-Memory table in SQL Server 2014, it would fail with an error.

CREATETABLE dbo.MyTableWithBLOB

(

       [ID] [int] IDENTITY(1,1)NOTNULL,

       [Date] [datetime] NOTNULL,

       [Status] [tinyint] NOTNULL,

       [Tax] [money] NOTNULL,

       [Comment] [nvarchar](max)NULL,

Constraint PK_SalesOrderID PRIMARYKEYNONCLUSTEREDHASH (ID)

       WITH (BUCKET_COUNT= 10000)

)WITH (MEMORY_OPTIMIZED=ON,DURABILITY= SCHEMA_AND_DATA)

GO

SQL SERVER 2014

Msg 10794, Level 16, State 84, Line 1

The type ‘nvarchar(max)’ is not supported with memory optimized tables.

SQL SERVER 2016Since VARCHAR(MAX) is now supported, the table is created without any error.

This enhancement would allow architects to move tables which have VARCHAR(MAX) data types to be in-memory tables.

Native Compilation Enhancements

As explained in brief, we can access In-memory tables using Interpreted method or via natively compiled stored procedures. Here is the sample of natively compiled stored procedure.

create table dbo.OrderTable

(OrderNumber integer not null primary key nonclustered,

CustomerCode datetime not null,

OrderDate nvarchar(5) not null,

comments varchar(max)

)

with (memory_optimized=on)

go

create procedure dbo.SampleNativelyCompiled(@OrderNo integer, @CustomerCode varchar(100))

with native_compilation, schemabinding, execute as self

as

begin atomic with

(transaction isolation level = snapshot,

language = N'English')

declare @OrderDate datetime = getdate();

declare @Comments varchar(max) = 'From WebSite';

insert into dbo.OrderTable(OrderNumber, CustomerCode, OrderDate, Comments) values (@OrderNo, @CustomerCode, @OrderDate,@Comments);

end

go

If we run above piece of code in a database which is enabled for In-Memory, we get below error

Msg 10794, Level 16, State 84, Procedure SampleNativelyCompiled, Line 18

The type ‘varchar(max)’ is not supported with natively compiled stored procedures.

If we create same procedure in SQL Server 2016, it works without any error.

In short, along with column name in table, SQL Server 2016 also supports LOB types [varchar(max), nvarchar(max), and varbinary(max)] for parameters and variables for natively compiled stored procedures.

Datavail Script: Terms & Conditions

By using this Oracle upgrade software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.