How to fix Error 10106 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 10106

Why This Error Happens

Error 10106 occurs when you attempt to create an indexed (materialized) view on a standard view that references an internal system rowset provider, such as views built on top of system catalog views, dynamic management views (DMVs), or certain linked server/OLE DB rowset providers. SQL Server’s indexed view engine requires deterministic, persistable base table data to maintain the clustered index, and internal system rowset providers do not expose the metadata needed to guarantee this determinism. This typically happens when a view indirectly joins against system objects (like sys.objects, sys.dm_* views, or OPENROWSET/OPENQUERY results) rather than purely user-defined base tables.

Step-by-Step Resolution Guide

Step 1: Identify the Offending View Definition

  • Run sp_helptext 'SchemaName.ViewName' in SSMS to inspect the full view definition.
  • Look specifically for references to system catalog views (sys.*), DMVs (sys.dm_*), INFORMATION_SCHEMA views, or external rowset functions like OPENROWSET, OPENQUERY, or OPENDATASOURCE.
  • Check nested views — the error can originate from a view-within-a-view that silently pulls in system metadata.

Step 2: Refactor the View to Remove System Rowset Dependencies

  • Rewrite the view so it only references standard user tables and persisted base tables, not system catalog or dynamic management objects.
  • If you need system metadata values (e.g., object IDs, row counts), pre-compute them into a regular user table via a scheduled job or stored procedure, then join to that user table instead.
  • Avoid wrapping the view around linked server queries or external OLE DB providers that return non-persistable rowsets.

Step 3: Create the Indexed View Correctly

  • Ensure the view is created WITH SCHEMABINDING.
  • Confirm all base tables referenced are regular disk-based tables owned by the same schema context.
  • Run the unique clustered index creation again:
CREATE UNIQUE CLUSTERED INDEX IX_ViewName_Clustered
ON SchemaName.ViewName (KeyColumn);

Step 4: Validate SET Options and Determinism Requirements

  • Verify the session creating the view had the required SET options enabled: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and NUMERIC_ROUNDABORT OFF.
  • Re-run sp_helptext on the corrected view to confirm no system rowset references remain before attempting index creation again.

Alternative or Verification Steps

  • To verify the fix, re-run the CREATE UNIQUE CLUSTERED INDEX statement against the revised view — success with no Error 10106 confirms the system rowset dependency has been eliminated.
  • If an indexed view is not strictly required, consider using a regular non-indexed view or converting the logic into a scheduled ETL job that materializes results into a physical staging table instead.