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

by

Troubleshooting Guide: Error 1985 — Cannot Create Index/Statistics on View Containing XML Data Type Methods

Why This Error Happens

This error occurs because SQL Server 2025 does not allow indexed views (or statistics) to be created on views that reference XML data type methods, such as .value(), .query(), .nodes(), or .exist(), directly in their SELECT list or WHERE clause. The query optimizer cannot guarantee deterministic results from XML methods, which is a strict requirement for indexed views since the index must persist a reliable, repeatable result set. This is a design limitation rather than a bug, and it typically surfaces when a view definition is altered to include XML column shredding logic without accounting for indexing restrictions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Method in the View Definition

  • Run sp_helptext 'YourViewName' in SSMS or Azure Data Studio connected to your SQL Server 2025 instance.
  • Scan the output for XML method calls like .value('(/Root/Element)[1]', 'nvarchar(50)') or .nodes('/Root/Element').
  • Confirm the view was marked WITH SCHEMABINDING and that an attempt was made to run CREATE UNIQUE CLUSTERED INDEX on it.

Step 2: Refactor the View to Remove Inline XML Methods

  • Move the XML shredding logic out of the view and into a persisted computed column on the base table instead:
  ALTER TABLE dbo.YourBaseTable
  ADD ExtractedValue AS CAST(XmlColumn.value('(/Root/Element)[1]', 'nvarchar(50)') AS nvarchar(50)) PERSISTED;
  • Update the view to simply SELECT the new persisted computed column instead of calling the XML method directly.
  • Re-run CREATE VIEW ... WITH SCHEMABINDING to rebuild the view against the updated base table schema.

Step 3: Recreate the Index Without XML Dependency

  • Navigate to SSMS Object Explorer and expand Databases > YourDB > Views > YourViewName.
  • Right-click Indexes and select New Index, or run:
  CREATE UNIQUE CLUSTERED INDEX IX_YourView_PK
  ON dbo.YourViewName (YourKeyColumn);
  • Confirm the index build completes without raising Error 1985 in the Messages pane.

Step 4: Validate Schema Binding Integrity

  • Check that no other dependent objects (triggers, other views, or stored procedures) still reference the removed XML method inline.
  • Run sys.sql_expression_dependencies against the view object to confirm clean dependency chains.
  • Restart the SQL Server service only if schema cache inconsistencies persist after the change (Services.msc > SQL Server (MSSQLSERVER) > Restart).

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes and sys.views to confirm the index status is is_disabled = 0 and the view compiles cleanly with sp_refreshview 'YourViewName'.
  • If refactoring isn’t feasible, bypass indexing on the view entirely and instead create a nonclustered index directly on the persisted computed column in the base table to achieve similar query performance gains.