Database Reference
In-Depth Information
Data Row Structure
Data and index formats in memory-optimized tables are different from those in on-disk tables. Storage is optimized
for byte-addressable memory using in-memory pointers rather than for block-addressable disk data using in-file
offsets. With the exception of range indexes, which we will discuss later, in-memory objects do not use in-memory
data pages. Data rows have pointers to the next row in the row chain.
The 8,060-byte limit on the maximum row size still applies. Moreover, memory-optimized tables do not support
off-row storage, which limits the data types that can be used in tables. As of the first release of in-memory OLTP, only
the following data types are supported:
bit
Integer types:
tinyint
,
smallint
,
int
,
bigint
Floating point types:
float
,
real
,
numeric
,
and
decimal
Money types:
money
and
smallmoney
Date/time types:
smalldatetime
,
datetime
,
datetime2
,
date
, and
time
uniqueidentifiers
Non-LOB string types:
(n)char(N)
,
(n)varchar(N)
, and
sysname
Non-LOB binary types:
binary(N)
and
varbinary(N)
As already mentioned, you cannot use data types that can use LOB storage in on-disk tables, such as (
n)
varchar(max)
,
xml
,
clr
,
(n)text
, and
image
. Moreover, contrary to on-disk tables, there is no concept of row-overflow
storage and the entire row must fit into 8,060 bytes, including variable-length data. It is impossible to create
memory-optimized tables with a row that could exceed that size, for example a row with two
varchar(5000)
columns.
Figure
32-2
illustrates the structure of a data row in a memory-optimized table. As you can see, it consists of two
sections:
Row Header
and
Payload
.
Figure 32-2.
The structure of a data row in a memory-optimized table
A SQL Server instance maintains the
Global Transaction Timestamp
value, which is auto-incremented at the time
of the transaction pre-commit validation (more on this later), and it is unique for every committed transaction. The
first two 8-byte elements in the row header,
BeginTs
and
EndTs
, define the data row lifetime.
BeginTs
stores the
Global
Transaction Timestamp
of the transaction that inserted a row, and
EndTs
stores the timestamp of the transaction that
deleted a row. A special value, called
Infinity
, is used as
EndTs
for the rows that have not been deleted.
In addition,
BeginTs
and
EndTs
control the visibility of a row for a transaction. A transaction can see a row only
when the transaction timestamp (Glo
bal Transaction Timestamp
value at the moment the transaction starts) is
between
BeginTs
and
EndTs
timestamps of the row.