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

by

Troubleshooting Guide: SQL Server Error 1964 – Imprecise Constant in Indexed View

Why This Error Happens

This error occurs when SQL Server attempts to create an index on a view that contains a constant expression using an imprecise data type, such as float or real. Indexed views require deterministic and precise expressions because the index must store exact computed values on disk. Since imprecise data types can produce slightly different results depending on floating-point rounding, SQL Server blocks the index creation to prevent data integrity issues.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic Constant in the View Definition

  • Open SSMS and run sp_helptext 'YourViewName' to review the full view script.
  • Look for numeric literals or expressions involving FLOAT, REAL, or implicit conversions to these types.
  • Check calculated columns or WHERE clauses for constants like 3.14 or 1.0E5 without explicit precise typing.

Step 2: Modify the View to Use Precise Data Types

  • Replace imprecise constants with precise types such as DECIMAL, NUMERIC, INT, or MONEY.
  • Explicitly cast ambiguous constants using CAST(3.14 AS DECIMAL(10,2)) instead of relying on default float inference.
  • Update the view using ALTER VIEW YourViewName WITH SCHEMABINDING AS SELECT ... ensuring all expressions resolve to deterministic, precise types.

Step 3: Recreate the View with Schema Binding Enabled

  • Ensure the view includes the WITH SCHEMABINDING option, as indexed views require this.
  • Confirm that SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON were active when the view was created (required for indexed views).
  • Run the CREATE UNIQUE CLUSTERED INDEX statement again on the corrected view.

Step 4: Rebuild the Index on the Updated View

  • Execute:
CREATE UNIQUE CLUSTERED INDEX IX_YourView_Index
ON YourViewName (YourColumn);
  • Check for any additional errors related to other non-deterministic functions (e.g., GETDATE(), RAND()) that may also block index creation.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes and sys.views to confirm the index now exists without error: SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('YourViewName');
  • If modifying the view isn’t feasible, consider removing the indexed view requirement and instead use a standard (non-indexed) view or materialize the data via a scheduled ETL job into a physical table with precise data types.