Home > In-MemoryOLTP > In-Memory OLTP –Part 6

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.

 

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
Categories: In-MemoryOLTP
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: