Memory-Optimized OLTP Tables and Procedures - SQL Server Query Performance Tuning - page 474

CONSTRAINT DF_Address_ModifiedDate DEFAULT (GETDATE())
)
WITH (
MEMORY_OPTIMIZED=
ON,
DURABILITY =
SCHEMA_AND_DATA);

This creates a durable table in the memory of the system using the disk space you defined to retain a durable
copy of the data, ensuring that you won't lose data in the event of a power loss. It has a primary key that is an
IDENTITY value just like with a regular SQL Server table (although, to use IDENTITY instead of SEQUENCE , you will be
surrendering the capability to set the definition to anything except (1,1) in this version of SQL Server). But, the index
definition is not clustered. Instead it's NON-CLUSTERED HASH . I'll talk about indexing and things like the BUCKET_COUNT
in the next section. You'll also note that I had to comment out two columns, SpatialLocation and rowguid . These are
using data types not available with in-memory tables. Finally, the WITH statement lets SQL Server know where to place
this table by defining MEMORY_OPTIMIZED=ON . You can make an even faster table by modifying the WITH clause to make
the DURABILITY=SCHEMA_ONLY . This allows data loss but makes the table even faster since nothing gets written to disk.
There are a number of unsupported data types that could prevent you from taking advantage of in-memory tables.

•

XML

•

ROWVERSION

•

SQL_VARIANT

•

HIERARCHYID

•

DATETIMEOFFSET

•

GEOGRAPHY/GEOMETRY
User-defined data types

•

• LOB, which includes text and ntext as well as all the MAX types of varchar and binary

In addition to data types, you will run into other limitations. I'll talk about the index requirements in the
“In-Memory Indexes” section. You can't create a foreign key reference to an in-memory table. This means all
referential integrity will have to come from the coding side of the application.
Once a table is created in-memory, you can access it just like normal. If I were to run a query against it now,
it wouldn't return any rows, but it would function.

SELECT a.AddressID
FROM dbo.Address AS a
WHERE a.AddressID = 42;

So, to experiment with some actual data in the database, go ahead and load the information stored in
Person.Address in AdventureWorks into the new table that's stored in-memory in this new database.

CREATE TABLE dbo.AddressStaging(
AddressLine1 nvarchar(60) NOT NULL,
AddressLine2 nvarchar(60) NULL,
City nvarchar(30) NOT NULL,
StateProvinceID int NOT NULL,
PostalCode nvarchar(15) NOT NULL
);