Month: June 2016

Database Snapshot and InMemory Tables

Database Snapshots are very useful when you need to test some quick changes without having to take backup and, also, they provide way to access the mirrored database to serve READ-ONLY queries.

However, if you have InMemory FileGroup, which is required for InMemory Tables, you cannot use Database Snapshot. I tired this on SQL 2016 RTM version.

USE [Master]
Go
CREATE DATABASE [InMemoryDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'InMemoryDB', FILENAME = N'C:\Temp\InMemoryDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP [InMemoryDB] CONTAINS MEMORY_OPTIMIZED_DATA
( NAME = N'InMemoryFile', FILENAME = N'C:\Temp\InMemoryFile' )
LOG ON
( NAME = N'InMemoryDB_log', FILENAME = N'C:\Temp\InMemoryDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
--Creating Snapshot Errors Out
CREATE DATABASE InMemory_Snap ON
( NAME = InMemoryDB, FILENAME =
'C:\Temp\InMemory_Snap.ss' )
AS SNAPSHOT OF InMemoryDB;
GO
Below is the error you will get:

Capture

Advertisement

Cannot update primary key in InMemory Tables

InMemory Tables are introduced in SQL 2014 and they are lot improved in SQL 2016.

While the surface area has improved in SQL 2016 compared to 2014 such as Foreign key constraints can be defined between Inmemory Tables, Indexes can have NULL columns, table can be altered etc.

There are still some restrictions in what you can do with SQL 2016 InMemory Tables and one such thing is, modifying the primary key of the table. Typically, we should not be modifying the primary key of the table but if there is ever a need to do that, it cannot be done in SQL 2016 InMemory Tables.

USE [Master]
Go
CREATE DATABASE [InMemoryDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'InMemoryDB', FILENAME = N'C:\Temp\InMemoryDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP [InMemoryDB] CONTAINS MEMORY_OPTIMIZED_DATA
( NAME = N'InMemoryFile', FILENAME = N'C:\Temp\InMemoryFile' )
LOG ON
( NAME = N'InMemoryDB_log', FILENAME = N'C:\Temp\InMemoryDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

USE InMemoryDB
Go
Create Table InMemoryTable(Sno int not null primary key nonclustered hash with (bucket_count=1000),sname varchar(20))
with (memory_optimized=ON,Durability=Schema_ONLY)

Insert into InMemoryTable
Values (1,'SomeName')

---Errors out
Update InMemoryTable set sno=2 where Sno=1
You will get error as below:
Capture