We know that SQL Server requires an additional file group that is marked as MEMORY_OPTIMIZED_FILEGROUP and a data file associated with it if memory-optimized tables need to be created. We know for sure that memory-optimized tables that are created as SCHEMA_AND_DATA require data files to write data to disk because they are coexist with disk-based tables but tables that are created as SCHEMA_ONLY require the same?
Memory-optimized tables that are created as non-durable (SCHEMA_ONLY) maintain both data and indexes in memory. Therefor, theorically it does not need anything to be written to disk. However, if you try to create a non-durable memory-optimized table without adding a memory-optimized-file-group, you get the following message;
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.
This says even for non-durable tables, it needs the file group. Although it does not maintain data in the disk, it has to create the table as the schema has to be maintained. Therefore, regardless of the type of memory-optimized tables, file group with MEMORY_OPTIMIZED_FILEGROUP option should be added to the database with a data file before creating memory-optimized tables.
No comments:
Post a Comment