Database Reference
In-Depth Information
Listing 32-2.
Creating a memory-optimized table
create table dbo.Customers
(
CustomerID int not null
constraint PK_Customers
primary key nonclustered
hash with (bucket_count = 100000),
Name varchar(128)
collate Latin1_General_100_BIN2
not null,
City varchar(64)
collate Latin1_General_100_BIN2
not null,
SSN char(9) not null,
DateOfBirth date not null,
index IDX_Customers_City
nonclustered hash(City)
with (bucket_count = 10000),
index IDX_Customers_Name
nonclustered(Name)
)
with (memory_optimized = on, durability = schema_and_data)
Each memory-optimized table has a
DURABILITY
option. The default
SCHEMA_AND_DATA
option indicates that the
data in the tables is fully durable and persists on disk for recovery purposes. Operations on such tables are logged in
the database transaction log, which allows SQL Server to support database transactional consistency and recreate the
data in the event of a SQL Server crash or unexpected shutdown.
SCHEMA_ONLY
is another option, which indicates that data in memory-optimized tables is not durable and would
be lost in the event of a SQL Server restart or crash. Operations against non-durable memory-optimized tables are not
logged in the transaction log. Non-durable tables are extremely fast and can be used if you need to store temporary
data, in use-cases similar to temporary tables in
tempdb
.
Finally, it is important to note that every text column that participates in the index must have
BIN2
collation.
These collations are case- and accent-sensitive, which could introduce some side effects, especially if you convert
existing tables to be memory-optimized.
High-Availability Technology Support
Memory-optimized tables are fully supported in an AlwaysOn Failover Cluster and Availability Groups, and with Log
Shipping. However, in the case of a Failover Cluster, data from durable memory-optimized tables must be loaded into
memory in case of a failover, which could increase failover time.
In the case of AlwaysOn Availability Groups, only durable memory-optimized tables are replicated to secondary
nodes. You can access and query those tables on the readable secondary nodes if needed. Data from non-durable
memory-optimized tables, on the other hand, is not replicated and will be lost in the case of a failover.
You can set up transactional replication on databases with memory-optimized tables; however, those tables
cannot be used as articles in publications.
In-memory OLTP is not supported in database mirroring sessions. This does not appear to be a big limitation,
however. In-memory OLTP is an Enterprise Edition feature, which allows you to replace database mirroring with
AlwaysOn Availability Groups.
