Create In-Memory Database in SQL Server 2014
This SQL Server 2014 tutorial shows to create SQL Server in-memory database and issues to consider like MEMORY_OPTIMIZED_DATA file group and *_BIN2 collation for indexes on memory optimized tables.
In-Memory database in SQL Server 2014 has different data file group and data file structure when compared with disk-optimized databases.
Since data files and data file groups of a in-memory optimized database is important for database administrators and SQL developers to create memory optimized objects and manage them, I wanted to make an overview on these in this SQL Server tutorial.
Before starting this SQL Server tutorial, I suggest SQL Server database developers and administrators (DBAs) to download SQL Server 2014 on install it on their test servers.
Following database create script is a simple CREATE DATABASE command which enables developers and SQL database admins to create a new in-memory database.
There are a few points to be considered when SQL developers or administrators use below script to create a in-memory database on SQL Server 2014 instance
First of all, please note that besides the data file and the log file there is an additional database file which will be used to manage in-memory data of the database.
Also this in-memory data file should be created on a Memory Optimized Data file group
You will see the definition FILEGROUP [file group name] CONTAINS MEMORY_OPTIMIZED_DATA which will lead to creation of a new file group which will contain the in-memory data file.
CREATE DATABASE MemoryDB
-- data file
ON PRIMARY
(
NAME = N'MemoryDBDataFile',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MemoryDBDataFile.mdf'
),
-- memory file and filestream data file group as container
FILEGROUP [InMemoryFG] CONTAINS MEMORY_OPTIMIZED_DATA
(
NAME = N'MemoryDBInMemoryData',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MemoryDBInMemoryData'
)
-- log file
LOG ON
(
NAME = N'MemoryDBLogFile',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MemoryDBLogFile.ldf'
)
Perhaps following screenshots from the SQL Server 2014 SQL Server Management Studio (SSMS) will help you visualize the file groups and data files for a in-memory database.
Here is the file groups created after above database create sql command
And here is the data files created at the same time
If you look at the database file groups, you will there can be three kinds of database file groups on a SQL Server 2014 in-memory optimized database.
These file group types can be listed as follows:
Rows -- for data files on physical disk
Filestream -- for filestream data like files, images, etc.
Memory Optimized Data -- for data which will be managed in memory optimized tables
It is interesting that memory optimized data file groups also contain filestream files like filestream file groups.
And as seen in the data files screenshot, in-memory data file has Filestream Data type and placed in the Memory Optimized Data file group.
To use the benefits of a in-memory processing features of SQL Server 2014, the database database must have a MEMORY_OPTIMIZED_DATA filegroup before the memory optimized table can be created.
So while creating a memory-optimized database, the database should have a filegroup marked with CONTAINS MEMORY_OPTIMIZED_DATA
If you want to create a memory optimized database using SQL Server Management Studio, you can follow the below screenshots in order.
In the solution explorer, right click on the Databases node and choose "New Database..."
First of all, navigate to the Filegroups tab where data file groups are defined. At the bottom section of the screen, you will see a part for "MEMORY OPTIMIZED DATA" filegroups. Click on the "Add Filegroup" button and enter the name of the new file group. Don't forget that we will use this Memory Optimized Data file group as a container for the in-memory file.
As the second step, go to the General tab.
In the database files screen you will see data file for disk optimized files and log file already defined.
Click on the Add button. This will enable you to create a new data file.
Provide a name for the new data file. Choose the File Type as FILESTREAM Data.
Filegroup will be set as the recently created data file group name.
As the last step but one of most important configurations is the Collation settings for the in-memory database.
Choosing the right collation is very important since for indexes on memory optimized table character columns, character columns should be defined in *_BIN2 collation. Otherwise, while creating index on character columns, errors like below will arise.
Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.
Although for creating indexes, character data columns must use one of the *_BIN2 collations on the memory-optimized table, database default collation can be set different.
On such cases table column collation can be set seperately using such a COLLATE hint: "collate Latin1_General_BIN2 NOT NULL"
Two general collations for memory optimized databases in SQL Server can be Latin1_General_100_BIN2 and Latin1_General_BIN2. Of course there are new database collation types of *_BIN2 collations for each country or specific character sets. For example, storing and working with Turkish characters, database administrators can use Turkish_100_BIN2 and Turkish_BIN2