Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

In Memory OLTP Changes in SQL Server 2016


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


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.

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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools