Memory Optimized Tables (MOT) Part 2

   Please check the Introduction to Memory Optimized Tables Part 1  to find out Memory Optimized Tables introduction and the requirements. As I explained in the earlier article, there are two types of Memory Optimized Tables (Durable, Non-Durable).

Here is a simple disk-based regular table.

Here is the script of the same table as Non-Durable Memory Optimized Table.

Memory Optimized Tables do not support CLUSTERED Indexes. You can create up to 8 NONCLUSTERED Indexes on a table. For each NONCLUSTERED Index, you need to specify the BUCKET_COUNT.

For BUCKET_COUNT value, Microsoft recommends between 1.5 and 2 times of the estimated number of unique values.

If we want to create same table as Durable, then our script will look like this.

As you can see, only difference between Durable and Non-Durable is the DURABILITY option. By giving SCHEMA_AND_DATA value, please visit the part 1 of this article for the differences between Durable and Non-Durable Memory Optimized Table.