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