In-Memory OLTP –Part 6

Table Types In-Memory


In-Memory table are similar to disk-based tables with few enhancement for In-Memory OLTP tables.To create a memory optimized table you need to add MEMORY_OPTIMIZED=ON clause with durability mode options (SCHEMA_ONLY and SCHEMA_AND_DATA)

The DURABILITY argument is not applicable to table types, which is a type from which table variables or table-valued parameters could be declared. Variables declared using memory-optimized table types are non-durable by definition.


INDEXES: You must specify column and table indexes as part of the CREATE TABLE statement. CREATE INDEX and DROP INDEX are not supported for memory-optimized tables.


BUCKET_COUNT : Indicates the number of buckets that should be created in the hash index. The maximum value for BUCKET_COUNT in hash indexes is 1,073,741,824. For more information about bucket counts, see Determining the Correct Bucket Count for Hash Indexes.

Bucket_count is a required argument.


Memory-optimized tables support hash indexes and memory-optimized nonclustered indexes. A memory-optimized table supports up to eight indexes. Dynamic hashing is not supported. For more information, see Guidelines for Using Indexes on Memory-Optimized Tables.

A nondurable memory-optimized table requires at least one index at the time of creation. A durable memory-optimized table requires a primary key that is used internally as a recovery index. Indexes cannot be added to an existing memory-optimized table.

Any column that is part of a primary key cannot be updated.



USE [AdventureWorks2014]

CREATE TABLE [Sales].[SalesOrderDetail_imoltp](
   [SalesOrderID] [int] NOT NULL,
   [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
   [CarrierTrackingNumber] [nvarchar](25) NULL,
   [OrderQty] [smallint] NOT NULL,
   [ProductID] [int] NOT NULL,
   [SpecialOfferID] [int] NOT NULL,
   [UnitPrice] [money] NOT NULL,
   [UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount1]  DEFAULT ((0.0)),
   [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderDetail_ModifiedDate1]  DEFAULT (getdate()),

   CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID_1] PRIMARY KEY NONCLUSTERED  
      [SalesOrderID] ASC,
      [SalesOrderDetailID] ASC
   INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID_2] NONCLUSTERED HASH ([SalesOrderID],[SalesOrderDetailID]) 
  WITH (BUCKET_COUNT = 1048576),
   INDEX [IX_SalesOrderDetail_ProductId_1] NONCLUSTERED ([ProductId] ASC)


INSERT DATA into Sales.SalesOrderDetail_imoltp

SET IDENTITY_INSERT  Sales.SalesOrderDetail_imoltp ON
insert into Sales.SalesOrderDetail_imoltp 
select SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,
from sales.SalesOrderDetail
SET IDENTITY_INSERT  Sales.SalesOrderDetail_imoltp OFF

Leave a Reply

Your email address will not be published.