Introduction
SQL Server 2014 introduced memory-optimized tables. This is fully durable by default. Fully durable transactions on this type of table are fully Atomic, Consistent, Isolated and Durable (ACID). The primary storage for the memory-optimized tables is main memory and it resides in memory. All the rows of this table are read from and written to memory. The second copy of the table data is maintained on disk for durability purposes. Data within a memory-optimized table is read from the disk only during the database recovery.
A memory-optimized table supports delayed durability. Delayed durable transaction data is saved on disk later after the transaction has been committed and control has been returned to the application. It might cause data loss if the server crashes or fails.
The following are the two types of memory-optimized tables:
- SCHEMA_AND _DATA
- SCHEMA_ONLY
The SCHEMA_AND_DATA memory-optimized table is a table that resides in memory where the data is also available after a SQL Server crash, a shutdown or a restart. The SCHEMA_ONLY memory-optimized table is a table that only persists the schema of the table and the data only resides in memory. In other words, the data does not persist if SQL Server crashes or the instance is stopped or restarted. SCHEMA_ONLY will be more useful for a staging table in a wherehouse.
Memory-optimized tables can be accessed with traditional TSQL as well as natively compiled Stored Procedure. The data storage of a memory-optimized table is free form data rows that are linked through one or more in-memory indexes. There are no page structures for the data rows as for traditional tables (disk based table). If any change is made in any rows of the table then new row versions are created but no transaction log is generated at that time.
Create memory-optimized table
To create a memory-optimized table, the database must have a file group for memory-optimized data.
ALTER DATABASE [Database Name]
ADD FILEGROUP [File Group Name]
CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE [Database Name]
ADD FILE (NAME='Logical File Name', FILENAME='File Path')
TO FILEGROUP [File Group Name];
The syntax for creating memory-optimized table
CREATE TABLE MemoryOptimizedTable
(
Id INT NOT NULL,
Name VARCHAR(100) NULL,
CONSTRAINT PK_MemoryOptimizedTable PRIMARY KEY NONCLUSTERED HASH (Id) WITH (BUCKET_COUNT = 20000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
Here BUCKET_COUNT is the number of buckets that should be created within a hash index and the maximum value of this filed is 1073741824. HASH indexes are only supported with memory-optimized tables.
We can also create a memory-optimized table from a GUI the same as for a normal table.
![Memory Optimized Table in SQL Server 2014]()
The following are the limitations of memory-optimized tables:
- Memory-optimized tables do not support IDENTITY columns
- Memory-optimized tables do not support DML triggers
- We cannot define a FOREIGN KEY or CHECK constraints with a memory-optimized table
- A memory-optimized table does not support UNIQUE constraints other than the PRIMARY KEY
- The number of indexes per table is limited (a maximum of 8 indexes per table is possible)
- In memory-optimized tables, all columns in the index key must be non-nullable.
- TRUNCATE TABLE, MERGE, Dynamic and keyset cursor is not supported with memory-optimized tables.
- Cross-database queries and cross-database transactions are not supported with memory-optimized tables.
- TABLOCK, XLOCK, PAGLOCK, NOLOCK, READCOMMITTED and READCOMMITTEDLOCK is not supported with memory-optimized tables in interpreted T-SQL Stored Procedures.
- Data types, Datetimeoffset, geography, geometry, LOBs (varchar(max), image, XML, text and ntext) are not supported with memory-optimized tables.