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.
CREATE TABLE :
USE [AdventureWorks2014] GO 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) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO
INSERT DATA into Sales.SalesOrderDetail_imoltp
SET IDENTITY_INSERT Sales.SalesOrderDetail_imoltp ON insert into Sales.SalesOrderDetail_imoltp ( SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber, OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount, ModifiedDate ) select SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber, OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,ModifiedDate from sales.SalesOrderDetail SET IDENTITY_INSERT Sales.SalesOrderDetail_imoltp OFF