In-Memory OLTP Migration Checklist: Which Features Block You

An In-Memory OLTP migration checklist tells you which tables and procedures the engine will refuse. SQL Server Management Studio builds one for a whole database with a wizard. This post shows what the checklist reports, then tests the refusals one by one on SQL Server 2025.

What the Wizard Reports

In Object Explorer, right-click a database, choose Tasks, then Generate In-Memory OLTP Migration Checklists. The wizard asks for a folder. It also asks whether to check every table and stored procedure or only the ones you pick. It writes one HTML report per object.

Each report lists features that memory-optimized tables or native procedures do not support. It marks each check as passed or failed. A failed check carries a link to help. For a single table, the Memory Optimization Advisor gives a similar list. It then walks you through the move. PowerShell offers Save-SqlMigrationReport for scripts.

I did not click through the wizard for this post. SSMS 22 on my machine still ships the wizard, since its assembly is installed. The tests below go behind the report. They ask SQL Server 2025 for each feature and record the answer.

Treat the results as an In-Memory OLTP migration checklist that you can rerun. Each line holds a feature, the message SQL Server gives and a fix. Keep the list with the project. Update it after every upgrade, because newer versions have loosened some rules.

Read the reports of your busiest tables first. A failed check on a hot table decides the whole project. Small lookup tables can wait.

Set Up a Test Database

A memory-optimized table needs a database with a memory-optimized filegroup. This script creates both, in the default data folder.

IF DB_ID(N'SqlXtpChecklistDemo') IS NULL CREATE DATABASE SqlXtpChecklistDemo;
GO
USE SqlXtpChecklistDemo;

DECLARE @sql nvarchar(max) = N'ALTER DATABASE SqlXtpChecklistDemo ADD FILEGROUP XtpFG CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE SqlXtpChecklistDemo ADD FILE (NAME = N''XtpFiles'', FILENAME = N''' + CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS nvarchar(260)) + N'SqlXtpChecklistDemo_xtp'') TO FILEGROUP XtpFG;';
IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = 'FX') EXEC (@sql);

Next comes a small helper. It runs one statement, catches the error, and logs the message. A memory-optimized table named Plain gives the tests something to work on.

CREATE TABLE dbo.TryLog (Seq int IDENTITY(1,1) PRIMARY KEY, Feature nvarchar(60) NOT NULL, Outcome nvarchar(300) NOT NULL);
GO
CREATE PROCEDURE dbo.TryIt @Feature nvarchar(60), @Stmt nvarchar(max) AS
BEGIN TRY
    EXEC (@Stmt);
    INSERT INTO dbo.TryLog (Feature, Outcome) VALUES (@Feature, N'Created');
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    INSERT INTO dbo.TryLog (Feature, Outcome) VALUES (@Feature, CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE()));
END CATCH;
GO
CREATE TABLE dbo.Plain (ID int NOT NULL PRIMARY KEY NONCLUSTERED, Name varchar(50) NOT NULL) WITH (MEMORY_OPTIMIZED = ON);

What Blocks a Table

The first test is a database DDL trigger. Memory-optimized tables cannot be created while one exists. The script creates a trigger, tries a table, and drops the trigger.

CREATE TRIGGER trg_BlockCreate ON DATABASE FOR CREATE_TABLE AS RETURN;
GO
EXEC dbo.TryIt N'CREATE TABLE while a database DDL trigger exists', N'CREATE TABLE dbo.t16 (ID int NOT NULL PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON)';
GO
DROP TRIGGER trg_BlockCreate ON DATABASE;

Now nineteen more cases, one per line. Most fail and six work.

EXEC dbo.TryIt N'Clustered primary key', N'CREATE TABLE dbo.t01 (ID int NOT NULL PRIMARY KEY CLUSTERED) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'xml column', N'CREATE TABLE dbo.t02 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, Doc xml) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'Sparse column', N'CREATE TABLE dbo.t03 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, Note varchar(20) SPARSE NULL) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'geography column', N'CREATE TABLE dbo.t04 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, Place geography) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'rowversion column', N'CREATE TABLE dbo.t05 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, RV rowversion) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'IDENTITY(5,2)', N'CREATE TABLE dbo.t06 (ID int IDENTITY(5,2) NOT NULL PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'Filtered index', N'CREATE TABLE dbo.t07 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, Q int, INDEX ix_q NONCLUSTERED (Q) WHERE Q > 0) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'DATA_COMPRESSION', N'CREATE TABLE dbo.t08 (ID int NOT NULL PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON, DATA_COMPRESSION = PAGE)';
EXEC dbo.TryIt N'CREATE inside a transaction', N'BEGIN TRAN; CREATE TABLE dbo.t09 (ID int NOT NULL PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON); COMMIT';
EXEC dbo.TryIt N'AFTER trigger without native compilation', N'CREATE TRIGGER dbo.trg_Plain ON dbo.Plain AFTER INSERT AS SELECT 1';
EXEC dbo.TryIt N'TRUNCATE TABLE', N'TRUNCATE TABLE dbo.Plain';
EXEC dbo.TryIt N'MERGE into the table', N'MERGE dbo.Plain AS t USING (SELECT 1 AS ID) AS s ON t.ID = s.ID WHEN NOT MATCHED THEN INSERT (ID, Name) VALUES (s.ID, ''a'');';
EXEC dbo.TryIt N'CREATE INDEX statement', N'CREATE INDEX ix_name ON dbo.Plain (Name)';
EXEC dbo.TryIt N'IDENTITY(1,1)', N'CREATE TABLE dbo.t10 (ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'varchar(max) column', N'CREATE TABLE dbo.t11 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, Txt varchar(max)) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'Computed column', N'CREATE TABLE dbo.t12 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, A int, B AS A * 2) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'CHECK constraint', N'CREATE TABLE dbo.t13 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, Q int CHECK (Q > 0)) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'Foreign key to a memory-optimized table', N'CREATE TABLE dbo.t14 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, P int REFERENCES dbo.Plain (ID)) WITH (MEMORY_OPTIMIZED = ON)';
EXEC dbo.TryIt N'Index on varchar, default collation', N'CREATE TABLE dbo.t15 (ID int NOT NULL PRIMARY KEY NONCLUSTERED, Name varchar(30) NOT NULL INDEX ix_name NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON)';
SELECT Feature, Outcome FROM dbo.TryLog ORDER BY Seq;
TRUNCATE TABLE dbo.TryLog;

Six cases passed without an error. They were IDENTITY(1,1), a varchar(max) column and a computed column. The next two were a CHECK constraint and a foreign key to a memory-optimized table. The sixth was a nonclustered index on a varchar column.

Read the message number as a class and the text as the detail. Msg 10794 is the common one, and its text names the feature: a type, an option or an operation. Other numbers cover rules about keys, identity values, transactions and triggers.

The fixes follow from the messages. Change a clustered key to nonclustered. Replace an unsupported type with a supported one, or keep that column in a second disk-based table. Reset identity seeds to 1 and increments to 1. Drop the filtered index and keep its predicate in the query.

Run the create statement outside a transaction, and remove database DDL triggers while you migrate. Rewrite a trigger as a native trigger. Replace TRUNCATE TABLE with DELETE, and MERGE with separate insert, update and delete statements.

That list differs from what you find in older articles. The 2014 engine refused computed columns and foreign keys. SQL Server 2025 accepts both. Check the date of any list before you trust it.

One more rule needs its own test. A durable table must have a primary key. Without one, SQL Server stops with two messages, and the second one hides the cause.

CREATE TABLE dbo.NoKey (ID int NOT NULL, Name varchar(50)) WITH (MEMORY_OPTIMIZED = ON);

Msg 41321, Level 16, State 7, Line 1
The memory optimized table 'NoKey' with DURABILITY=SCHEMA_AND_DATA must have a primary key.
Msg 1750, Level 16, State 1, Line 1
Could not create constraint or index. See previous errors.

What Blocks a Native Procedure

A natively compiled procedure compiles to machine code, so its T-SQL surface is smaller. The second helper wraps the procedure boilerplate and drops each test procedure after the attempt. A plain disk-based table joins the cast.

CREATE PROCEDURE dbo.TryNative @Feature nvarchar(60), @Body nvarchar(max) AS
BEGIN TRY
    EXEC (N'CREATE PROCEDURE dbo.np WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N''us_english'') ' + @Body + N'; END');
    EXEC (N'DROP PROCEDURE dbo.np');
    INSERT INTO dbo.TryLog (Feature, Outcome) VALUES (@Feature, N'Created');
END TRY
BEGIN CATCH
    INSERT INTO dbo.TryLog (Feature, Outcome) VALUES (@Feature, CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE()));
END CATCH;
GO
CREATE TABLE dbo.DiskPlain (ID int NOT NULL PRIMARY KEY);

EXEC dbo.TryNative N'Plain SELECT', N'SELECT ID FROM dbo.Plain WHERE ID = 1';
EXEC dbo.TryNative N'CASE expression', N'SELECT CASE WHEN ID > 1 THEN 1 ELSE 0 END AS Big FROM dbo.Plain';
EXEC dbo.TryNative N'DISTINCT', N'SELECT DISTINCT Name FROM dbo.Plain';
EXEC dbo.TryNative N'UNION ALL', N'SELECT ID FROM dbo.Plain UNION ALL SELECT ID FROM dbo.Plain';
EXEC dbo.TryNative N'Disk-based table', N'SELECT ID FROM dbo.DiskPlain';
EXEC dbo.TryNative N'Common table expression', N'WITH c AS (SELECT ID FROM dbo.Plain) SELECT ID FROM c';
EXEC dbo.TryNative N'LIKE', N'SELECT ID FROM dbo.Plain WHERE Name LIKE ''a%''';
EXEC dbo.TryNative N'ROW_NUMBER', N'SELECT ROW_NUMBER() OVER (ORDER BY ID) AS rn FROM dbo.Plain';
EXEC dbo.TryNative N'OFFSET and FETCH', N'SELECT ID FROM dbo.Plain ORDER BY ID OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY';
EXEC dbo.TryNative N'MERGE', N'MERGE dbo.Plain AS t USING (SELECT 1 AS ID) AS s ON t.ID = s.ID WHEN NOT MATCHED THEN INSERT (ID, Name) VALUES (s.ID, ''a'')';
EXEC dbo.TryNative N'Cursor', N'DECLARE c CURSOR FOR SELECT ID FROM dbo.Plain';
EXEC dbo.TryNative N'INSERT with two rows of VALUES', N'INSERT INTO dbo.Plain (ID, Name) VALUES (1, ''a''), (2, ''b'')';
EXEC dbo.TryNative N'SELECT INTO', N'SELECT ID INTO #x FROM dbo.Plain';
SELECT Feature, Outcome FROM dbo.TryLog ORDER BY Seq;

Look closely at LIKE and the window function ROW_NUMBER. A search procedure or a paging procedure cannot go native as written. It can stay as a normal interpreted procedure.

Each refusal has a plain workaround. Write a subquery in place of a common table expression. Insert rows one statement at a time instead of one statement with many rows. Split MERGE into separate statements. Replace LIKE with range comparisons, or keep that procedure interpreted.

A disk-based table inside a native procedure is the largest decision of the list. The procedure can reach only memory-optimized tables. Moving the second table across is a project of its own, so decide it early.

Find Blockers in Your Own Database

The wizard gives the full list. This query gives a fast first pass over the blockers I tested. First, four sample disk-based tables, each with a different problem.

CREATE SCHEMA Legacy;
GO
CREATE TABLE Legacy.Customer (CustomerID int IDENTITY(1,1) PRIMARY KEY, Name varchar(50) NOT NULL, Profile xml NULL);
CREATE TABLE Legacy.Product (ProductID int IDENTITY(100,5) PRIMARY KEY, Name varchar(50) NOT NULL, Note varchar(100) SPARSE NULL);
CREATE TABLE Legacy.Shop (ShopID int PRIMARY KEY, Place geography NULL, RowVer rowversion);
CREATE TABLE Legacy.Ticket (TicketID int IDENTITY(1,1) PRIMARY KEY, Title varchar(50) NOT NULL);
GO
CREATE TRIGGER Legacy.trg_Ticket ON Legacy.Ticket AFTER INSERT AS SET NOCOUNT ON;

Now the finder. It reads the catalog only, so it is safe on any database.

SELECT s.name + N'.' + t.name AS TableName, c.name AS ItemName,
       CASE WHEN c.is_sparse = 1 THEN N'sparse column' WHEN c.is_rowguidcol = 1 THEN N'ROWGUIDCOL' ELSE ty.name END AS Blocker
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE t.is_memory_optimized = 0
  AND (ty.name IN (N'xml', N'sql_variant', N'hierarchyid', N'geography', N'timestamp', N'text') OR c.is_sparse = 1 OR c.is_rowguidcol = 1)
UNION ALL
SELECT s.name + N'.' + t.name, ic.name, CONCAT(N'identity(', CAST(ic.seed_value AS bigint), N',', CAST(ic.increment_value AS bigint), N')')
FROM sys.identity_columns AS ic
JOIN sys.tables AS t ON t.object_id = ic.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.is_memory_optimized = 0 AND (CAST(ic.seed_value AS bigint) <> 1 OR CAST(ic.increment_value AS bigint) <> 1)
UNION ALL
SELECT s.name + N'.' + t.name, tr.name, N'DML trigger'
FROM sys.triggers AS tr
JOIN sys.tables AS t ON t.object_id = tr.parent_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE tr.parent_class = 1 AND t.is_memory_optimized = 0
ORDER BY 1, 2;

Four tables, six blockers, and Ticket needs a native trigger or a rewrite. The query is a starting point. It covers only what I tested. Use the wizard for completeness.

A Fair Objection

You could say the wizard already builds this In-Memory OLTP migration checklist, so the T-SQL tests are redundant. Fair point. Use the checklist for the full list and for a report your team can read. The T-SQL gives you the exact message of your own build, and it checks a fix in seconds.

A passed checklist also says nothing about speed. A table can pass every check and still gain nothing from memory-optimized storage.

A Short Checklist

• Run the wizard on a restored copy, never on production.
• Fix keys first: clustered primary keys become nonclustered, and every durable table needs one.
• Replace unsupported types and sparse columns, then odd identity values and triggers.
• Start with small, hot tables. Leave large, cold ones on disk.
• Rerun the finder query after each change, and keep the log of messages.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlXtpChecklistDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlXtpChecklistDemo;

A passed checklist is not a faster database, it is permission to start testing.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.