Memory Optimized Table is a new concept introduced in SQL Server Hekaton. Memory optimized table is also a table but the major difference between memory-optimized tables and disk based tables is that the memory optimized table information is available in the memory and it does not require the pages to be read into cache from disk. The performance of memory optimized table is much higher than disk based table. Shall discuss its performance in the upcoming article.
Let me create the memory optimized table, but before proceeding with the creating memory table, we should know that whenever you create memory optimized table the database must have memory-optimized data filegroup.
Lets first create a database having memory-optimized data filegroup. Given below is the script.
CREATE DATABASE Sample_DB ON PRIMARY(NAME = [Sample_DB], FILENAME = 'C:\DATA\Sample_data.mdf', size=500MB) ,FILEGROUP [hekaton_demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA( NAME = [hekaton_demo_dir], FILENAME = 'C:\DATA\Sample_dir') LOG ON (name = [hekaton_demo_log] , Filename='C:\DATA\Sample_log.ldf', size=500MB) ; GO
Once you create the database having memory-optimized data filegroup then you can create memory optimized table. Given below is the script.
USE Sample_DB GO CREATE TABLE tbl_sample ( [ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), [Name] VARCHAR(50) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO
[…] tables. is one of the new error messages in SQL Server Hekaton. This error message is related to Memory optimized tables feature, a new type of table shipped with SQL Server […]
[…] my earlier article, I wrote a solution to create memory optimized table in a new database. But the question is how to create memory optimized table in an existing database. Let me explain […]
[…] table is a new concept to maintain records in table with high performance. I already discussed this new type of table earlier in my article. So, I kept on researching for this new feature and came across an issue. The […]
[…] tables is one of the new error messages in SQL Server Hekaton. This error message is related to Memory optimized tables feature, a new type of table shipped with SQL Server […]