To create an InMemory Table – we first have to create a Memory Optimized file group and then associate a “filestream” file type to this file group. After this, InMemory Table can be created.
The catch here(or atleast in SQL 2016 RTM\14 SP1) is you cannot remove the InMemory File or FileGroup even after you dropped all the Inmemory Objects. The only option to remove InMemory file is to script out all the database objects\data and drop the database and recreate with the scripts.
So, if you wanted try InMemory tables and later decide not use them and you can drop the objects but not the InMemory file or file group.
NOTE: InMemory File\File Group can be removed as long as no InMemory Table was ever created. The issue happens only when an InMemory Table was created.
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)
--Let's drop InMemory Table
Drop Table InMemoryTable
--Trying Removing the InMemory File\FileGroup
ALTER DATABASE [InMemoryDB] REMOVE FILE [InMemoryFile]
ALTER DATABASE [InMemoryDB] REMOVE FILEGROUP [InMemoryDB]
GO
You will get the below error message:
SQL Server Repository 