Using SQL Server Management Studio
Now I will show you how to create the same database using SQL Server Management Studio.
To create a database using SQL Server Management Studio I first right-click on the “Database” node, then select the “New Database” option from the menu. Doing this brings up the “New Database” window. In that window I identify the name of my OLTP database, so the window looks as in the following:
Then click on the “Filegroups” option in the left pane of this window. That brings up the filegroup window of the New Database dialog. In the filegroup window I click on the new “Add Filegroup” button under the “MEMORY_OPTIMIZE DATA” section of the right pane and then I enter the name of my new memory-optimized filegroup as shown in the screen shot below:
I then click on the “General” option in the left pane to add a new file in my new OLTP_OLTP filegroup. I do that by clicking on the “Add” button, then selecting a “File Type” of “FILESTREAM”, and then specifying the “Logical Name” of “OLTP_OLTP “ as shown in the screenshot below:
Now I will show the option to create a memory table using SQL Server Management Studio.
In the Object Explorer in SQL Server Management Studio click on the “Table” item and then hover over the “New” item that will display the “Memory Optimize Table…” option as displayed in the screenshot below:
Inserting Data into a Memory-Optimized Table
Let's start to test loading my Memory-Optimized tables. I decided to build a script that would load 100000 rows in each of my three tables. The following is the code that I used to test loading my Normal table and my two Memory-Optimized tables.
Here is the output of one of my test runs.
Step 5
As you can see, I was able to load my “Employee_Normal” table faster than my “Employee_Schema_And_Data” table. I ran my script multiple of times and sometimes loading rows into the “Employee_Schema_And_Data” table outperformed loading rows into my “Employee_Normal” table. But when it did outperform, it didn't do it by very much. Additionally you can see that inserting rows into my “Employee_Schema_Only” table outperformed the loading of data into the other two tables. No matter how many times I ran this script, the loading of the table that had a DURABILITY setting of SCHEMA_ONLY always out performed loading into the other two tables.
It is understandable that inserting data into my “Employee_Normal” and “Employee_Schema_And_Data” table might perform about the same. I say this because a Memory-Optimized table that has a DURABILITY mode of “SCHEMA_AND_DATA” needs to perform I/O to the checkpoint and transaction log when records are inserted, just like a normal table. This additional I/O when data is written to a table slows down the INSERT performance. This additional I/O allows for the “SCHEMA_AND_DATA” tables to be recoverable should I have a server crash or my server must be restarted. Whereas the rows inserted into my “Employee_Schema_Only” table will not be recovered should the SQL Server crash or be restarted. If inserting data into your table is important then you should consider using Memory-Optimized tables that have a DURABILTY setting of "Employee_Schema_Only”.
When SQL Server restarts or the database is brought back online the Memory-Optimized tables that are defined with the SCHEMA_AND_DATA DURABUILTY option will be read from the checkpoint and transaction log files and repopulated in memory. One thing to note is that while SQL Server repopulates memory with the SCHEMA_AND_DATA Memory-Optimized tables, your database will be in a recovering state and will be unavailable. You can see that in the following screen:
Note that my OLTP database is in the “In Recovery” state. Once all the data is loaded into memory for my Memory-Optimized tables than the database will become available.
For more details see In-Memory OLTP (In-Memory Optimization)
Summary
This article described how to create Memory-Optimized tables in SQL Server 2014.