SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server 2014 Download and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



Cannot create memory optimized tables in a database on SQL Server 2014

SQL Server 2014 enables database administrators to create memory optimized table if memory-optmized database file group and filestream database file is created appropriately. Otherwise, SQL Server engine can rise exception "Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup".

While I was trying to create memory-optimized table on SQL Server 2014 database, I got following error message:

Msg 41337, Level 16, State 0, Line 4
Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup.


Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup
Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup.

If you carefully check the above screenshot from database properties Filesgroups tab, you will notice that although we have defined a memory optimized data file group MemoryOptimizedFG, the number of FileStream files is 0.
This points database administrators the cause of the above error.
SQL Server database administrators have to create a FileStream Data file type database file before they create memory-optimized tables on SQL Server 2014 or on SQL Server 2016.

Add a new database file, which is in FileStream Data file type and choose the filegroup as the memory optimized file group you have created before.

SQL Server 2014 database filestream file for memory optimized table
SQL Server 2014 Database files for memory-optimized tables

Now you can execute following create memory optimized table SQL command

create table MemoryOptimizedTable (
 c1 int not null primary key nonclustered,
 c2 int
)
with (memory_optimized=on)





SQL Server Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers










Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems