In Memory OLTP Changes in SQL Server 2016

By:   |   Comments   |   Related: > SQL Server 2016


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


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.

2016 In Memory OLTP

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.

New Bucket count

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')

Verify Newly Created Index

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)

Add Multiple Columns and Indexes

Drop an index

Alter table TestOrders

Drop Index IX_newcolumn1 

Other Enhancements

Here are some additional changes in SQL Server 2016.

Feature SQL 2014 SQL 2016
Foreign Keys Not supported Supported
Check/Unique Constraints Not supported Supported
Parallelism Not supported Supported
Indexes on NULLable columns Not supported Supported
Maximum size of durable table 256 GB 2 TB
ALTER PROCEDURE / sp_recompile Not supported Supported
SSMS Table Designer Not supported Supported
Check/Unique Constraints Not supported Supported

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta 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

Comments For This Article

get free sql tips
agree to terms