In-Memory Optimized Tables - Getting Started with SQL Server 2014 Administration - page 36

The following code will create a memory-optimized table called t1 cart :
CREATE TABLE t1 (
T1ID int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_
COUNT=2000000),
UserId int NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_
COUNT=1000000),
CreatedDate datetime2 NOT NULL,
TotalPrice money
)
WITH (MEMORY_OPTIMIZED=ON)
GO
You can see here that when creating the table I have used Memory_optimized=ON ,
which tells the database engine when we want the table to be memory optimized.
You can insert rows to the table using native interpreted TSQL. You have two
durability settings that you can use when creating a memory-optimized table: the
SCHEMA_AND_DATA and SCHEMA . The SCHEMA_AND_DATA settings are the default
settings so if you don't specify a setting, the memory-optimized table gets created
as a durable table with data persisted to disk.
Memory-optimized tables and indexes
Memory-optimized tables support two types of indexes:
• Non-clustered hash indexes : These are used for point lookups.
• Non-clustered indexes : These are used for range and ordered scans.
Memory-optimized tables introduced a new concept called hash indexes . Hash
indexes created on memory-optimized tables do not make use of a traditional b-tree
structure like the one that the disk-based indexes utilize. Instead, the hash index is
stored in a hash table with linked lists used to connect all the rows that hash to the
same value, which are stored in Bw-Trees.
It is a requirement that a memory-optimized table should have an index created
on it and this is demonstrated in the CREATE TABLE statement associated with the
memory-optimized tables. An In-Memory Optimized table requires that create
table must specify an index to be created. You cannot have an unorganized
in-memory table like you can with disk-based heaps. You can see that we created
a hash index from the sample code that created the dbo.shoppingcart table.