In Memory OLTP Changes in SQL Server 2016

By: Rajendra Gupta   |   Comments   |   Related: > SQL Server 2016

Problem

SQL Server 2016 In Memory OLTP has some new enhancements and we we will look at some of these new features in this tip.

Solution

Below are changes in SQL Server 2016 In Memory OLTP.

New Standard Reports

In SQL Server 2014, to use In Memory OLTP reports, we needed to configure the Management Data Warehouse and the data collection process. In SQL Server 2016 reports are available in SSMS without setting up the MDW database. Below is a list of the reports.

Non-BIN2 Collation Support

It is now possible to use Non-BIN2 collations in index key columns. If we used a Non-BIN2 collation in SQL Server 2014 we got this error: The feature 'non-bin2 collation' is not supported with indexes on memory optimized tables.

Here is how we can create a Memory Optimized table with a Non-Bin2 collation in SQL Server 2016.

Create table TestOrders
(
[id] int identity(1,1) not null,
[customer_name]	nvarchar(10) not null,
[OrderDescription] varchar(50) COLLATE FRENCH_CI_AS Not Null  -- Non Bin2 Collation
Primary key nonclustered hash (id) with (Bucket_count=1024))
with (Memory_Optimized=on, Durability = SCHEMA_and_data)

ALTER Table Operations

In SQL Server 2014, once a memory-optimized table was created it could only be dropped and recreated if a change was needed. With SQL Server 2016, ALTER TABLE statements on index and schema changes is now supported. However, ALTER TABLE is an offline operation which requires more memory temporarily. The database application can continue to run and any operation that is accessing the table is blocked until the alteration process is completed.

Below are some examples.

Modify Bucket Count

To modify bucket_count for an existing index:

Alter table TestOrders

Alter Index [PK__TestOrde__3213E83EB07AF8C9] rebuild with (Bucket_count=2048)

Let's verify the new bucket count:

select index_id,name,Bucket_count
from sys.hash_indexes
where object_id=object_id('dbo.testorders')

We can see the bucket count is now 2048.

Add new column to existing table

Alter table TestOrders

Add newcolumn Int NULL

Add new index to existing table

Alter table TestOrders

Add index IX_Customername nonclustered ([Customername])

To verify new index:

select index_id,name,type_desc
from sys.indexes
where object_id=object_id('dbo.testorders')

Add multiple columns and indexes

Alter table dbo.testOrders

Add newcolumns1 int not null Default 1 with values,
Add newcolumns2 int not null Default 1 with values,
index IX_newcolumns1 (newcolumns1)

Drop an index

Alter table TestOrders

Drop Index IX_newcolumn1 

Other Enhancements

Here are some additional changes in SQL Server 2016.

Next Steps

• Read more about In Memory OLTP Getting started with In memory OLTP

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips