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

by

Troubleshooting Guide: Error 1953 – Index on View Cannot Be Created Due to Missing Arguments in Built-In Function

Why This Error Happens

This error occurs when SQL Server 2025 attempts to create an indexed view that references a built-in function requiring deterministic, fully-specified arguments, but one or more arguments were omitted or rely on implicit defaults. Indexed views require strict determinism, so functions like CONVERT(), CAST(), or date/time functions must have every optional parameter explicitly declared. This commonly happens after schema migrations or when T-SQL code written for earlier SQL Server versions is deployed to SQL Server 2025, where stricter view-indexing validation rules are enforced.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function Call in the View Definition

  • Open SQL Server Management Studio (SSMS) and run sp_helptext 'YourViewName' to retrieve the full view definition.
  • Scan for functions commonly affected, such as CONVERT, CAST, DATEADD, DATEDIFF, ROUND, or SUBSTRING.
  • Pay close attention to any function call missing a style, length, or precision argument (e.g., CONVERT(varchar, GetDate()) instead of CONVERT(varchar(23), GetDate(), 121)).

Step 2: Explicitly Specify All Required Arguments

  • Rewrite the view’s SELECT statement to include every optional parameter explicitly. For example, change CONVERT(VARCHAR, OrderDate) to CONVERT(VARCHAR(10), OrderDate, 101).
  • For ROUND(), ensure the length argument is specified: use ROUND(Price, 2) rather than ROUND(Price).
  • Save the corrected view script to a .sql file in a working directory such as C:\SQLScripts\ViewFixes\ for version control.

Step 3: Drop and Recreate the View with the Corrected Definition

  • Execute DROP VIEW YourViewName; in SSMS or via sqlcmd -S YourServer -d YourDatabase -i C:\SQLScripts\ViewFixes\DropView.sql.
  • Recreate the view using CREATE VIEW YourViewName WITH SCHEMABINDING AS ... ensuring SCHEMABINDING is applied, as it is mandatory for indexed views.
  • Confirm no errors are returned during view creation before proceeding to indexing.

Step 4: Recreate the Unique Clustered Index

  • Run CREATE UNIQUE CLUSTERED INDEX IX_YourView_Index ON YourViewName (YourColumn); to rebuild the index.
  • Verify the command completes without raising Error 1953 or related determinism errors (such as Error 1951).
  • Check SQL Server Error Logs located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG if the index creation fails silently.

Step 5: Validate Function Determinism Settings Server-Wide

  • Run the query SELECT OBJECTPROPERTY(OBJECT_ID('dbo.YourFunction'), 'IsDeterministic'); on any custom scalar functions used within the view.
  • If a user-defined function returns 0 (non-deterministic), modify it using the WITH SCHEMABINDING clause and ensure it does not call non-deterministic built-ins internally.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes and sys.views to confirm the index status is active: SELECT i.name, i.type_desc FROM sys.indexes i JOIN sys.views v ON i.object_id = v.object_id WHERE v.name = 'YourViewName';.
  • If the error persists, bypass indexing entirely by using a standard (non-indexed) view combined with a scheduled indexed temp table or materialized table refresh via SQL Server Agent Jobs as an alternative performance optimization strategy.