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

by

Troubleshooting Guide: Error 10142 – Cannot Create Index on View Containing APPLY

Why This Error Happens

This error occurs because SQL Server’s indexed view engine does not support the APPLY operator (CROSS APPLY or OUTER APPLY) within the view definition. Indexed views require deterministic, schema-bound logic that produces a result set without row-by-row correlated execution, and APPLY inherently relies on evaluating a table-valued expression for each row of the outer table, which violates this requirement. As a result, the SQL Server engine blocks the creation of a unique clustered index on any view that references this construct.

Step-by-Step Resolution Guide

Step 1: Identify the Offending APPLY Clause

  • Open the view definition using sp_helptext 'YourViewName'; in SQL Server Management Studio (SSMS).
  • Scan the FROM clause for CROSS APPLY or OUTER APPLY keywords.
  • Note the specific table-valued function (TVF) or subquery being invoked by the APPLY.

Step 2: Rewrite the View Logic Without APPLY

  • Replace CROSS APPLY with a standard INNER JOIN if the logic allows for a simple set-based join instead of row-by-row correlation.
  • If a Table-Valued Function (TVF) is being called via APPLY, try inlining the TVF’s logic directly into the view as a subquery or joined derived table.
  • Example fix: Convert SELECT a.*, b.val FROM TableA a CROSS APPLY dbo.GetVal(a.ID) b into SELECT a.*, b.val FROM TableA a JOIN dbo.ValuesTable b ON a.ID = b.ID.

Step 3: Alter the View with Schema Binding

  • Once the APPLY is removed, ensure the view is created WITH SCHEMABINDING to meet indexing prerequisites.
  • Run the following command to update the object: ALTER VIEW YourViewName WITH SCHEMABINDING AS SELECT...
  • Verify that two-part naming (e.g., dbo.TableName) is used for all referenced tables within the view definition.

Step 4: Re-create the Unique Clustered Index

  • Attempt to create the index again using: CREATE UNIQUE CLUSTERED INDEX IX_View_Name ON YourViewName (Column1);
  • Confirm the command executes without triggering Error 10142.

Alternative or Verification Steps

  • Verification: Query sys.indexes joined with sys.objects to confirm the clustered index now exists on the view (is_unique = 1).
  • Alternative: If the APPLY logic is too complex to refactor (e.g., requires row-by-row recursive logic), abandon the indexed view approach and instead materialize the data into a physical “summary” table using a scheduled SQL Agent Job or a trigger-based update strategy.