Troubleshooting Guide: Error 10149 – SNAPSHOT_MATERIALIZATION Index Cannot Be Created on View with Memory-Optimized Tables

Why This Error Happens

This error occurs because SQL Server does not support creating an index with SNAPSHOTMATERIALIZATION on a view when that view’s definition references one or more memory-optimized (In-Memory OLTP) tables. The SNAPSHOTMATERIALIZATION property relies on a materialization mechanism that is architecturally incompatible with the Hekaton storage engine used by memory-optimized tables. This is a design limitation rather than a configuration error, meaning no amount of permission changes or server tuning will resolve it—the view definition itself must be altered.

Step-by-Step Resolution Guide

Step 1: Identify the Memory-Optimized Tables in the View Definition

• Run sp_helptext 'YourViewName' in SQL Server Management Studio (SSMS) to inspect the full view definition.
• Cross-reference each table referenced in the view against sys.tables by checking the is_memory_optimized column:

  SELECT name, is_memory_optimized FROM sys.tables WHERE is_memory_optimized = 1;

• Note which table(s) are causing the conflict before proceeding.

Step 2: Remove or Modify the SNAPSHOT_MATERIALIZATION Clause

• Open the CREATE INDEX or CREATE VIEW script that is failing.
• Remove the WITH (SNAPSHOT_MATERIALIZATION = ON) option from the index definition.
• If the index is required for performance reasons, consider creating a standard clustered or nonclustered index instead, without the snapshot materialization property:

  CREATE UNIQUE CLUSTERED INDEX IX_YourView_Index 
  ON dbo.YourViewName (YourColumn);

Step 3: Redesign the View to Separate Memory-Optimized Logic

• If the materialized index is critical, refactor the view so it does not directly reference memory-optimized tables.
• Create an intermediate disk-based staging table that periodically copies data from the memory-optimized table (via a scheduled job or trigger).
• Point the view requiring SNAPSHOT_MATERIALIZATION at the disk-based staging table instead.

Step 4: Validate Database Compatibility Level

• Confirm the database is running on a compatibility level that supports your intended indexing strategy:

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If the compatibility level is outdated (below 160), update it using:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• After applying changes, re-run the CREATE INDEX statement and confirm it completes without Error 10149 appearing in the SQL Server Error Log or SSMS output pane.
• As an alternative, if memory-optimized tables are not strictly required for the underlying workload, consider converting them back to disk-based tables using CREATE TABLE ... WITH (MEMORY_OPTIMIZED = OFF) migration scripts, which eliminates the conflict entirely.