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

by

Troubleshooting Guide: Error 1966 – Cannot Create Index/Statistics on View with Imprecise Aggregate

Why This Error Happens

This error occurs because SQL Server 2025 does not allow indexed views (or statistics objects) to be created on views that contain “imprecise” aggregate functions, such as AVG, STDEV, STDEVP, VAR, or VARP. These aggregates rely on floating-point mathematics, which can produce slightly different results depending on processing order, making them incompatible with the deterministic requirements of a materialized index. The Query Optimizer blocks the index creation to prevent data integrity issues and inconsistent results between the indexed view and the base tables.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Aggregate Function

  • Run sp_helptext 'YourViewName' in SSMS to review the view’s underlying T-SQL definition.
  • Look specifically for AVG(), VAR(), VARP(), STDEV(), or STDEVP() within the SELECT statement.
  • Note the specific column and table these aggregates are referencing.

Step 2: Rewrite the View Using Precise Aggregates

  • Replace the imprecise aggregate with a combination of SUM() and COUNT_BIG(), which are deterministic and precise.
  • For example, instead of AVG(SalesAmount), modify the SELECT clause to include SUM(SalesAmount) AS Total, COUNT_BIG(SalesAmount) AS ItemCount.
  • Save this as a new query or update the existing view using ALTER VIEW dbo.YourViewName WITH SCHEMABINDING AS....

Step 3: Recreate the View with Schemabinding

  • Ensure the WITH SCHEMABINDING option is included in the CREATE VIEW or ALTER VIEW statement, as this is a mandatory prerequisite for indexing.
  • Verify that two-part naming conventions (e.g., dbo.TableName) are used for all referenced tables within the view.
  • Execute the script in SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.

Step 4: Create the Unique Clustered Index

  • Attempt to create the index again using: CREATE UNIQUE CLUSTERED INDEX IX_ViewName ON dbo.YourViewName (ItemCount);
  • Confirm the index creation completes without triggering Error 1966.
  • Check the SQL Server Error Log located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ for any residual schema binding warnings.

Alternative or Verification Steps

  • Verification: Query the view directly after indexing and calculate the average manually (SUM / COUNT_BIG) in your application layer or a secondary SELECT statement to ensure the math matches the original intended logic.
  • Alternative: If modifying the view is not feasible due to application dependencies, skip the indexed view approach entirely and rely on a standard non-indexed view combined with a NONCLUSTERED index on the underlying base table columns to improve query performance instead.