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

by

Troubleshooting Guide: SQL Server Error 1961 – Non-Deterministic Collation in Indexed Views

Why This Error Happens

This error occurs when SQL Server attempts to create an indexed view (or index on a view) that references a column using a collation cast which is marked as non-deterministic because it relies on OS-level collation rules rather than SQL Server’s internal binary collation logic. Indexed views require all expressions, including collation casts, to be deterministic so that SQL Server can guarantee consistent results for materialization and indexing. This typically happens when the column or expression uses a Windows collation (OS-dependent) instead of a binary or SQL Server collation, often introduced after a collation migration, database restore from a different server, or explicit use of COLLATE with a non-binary Windows collation in the view definition.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic Column and Collation

  • Open the view definition using sp_helptext 'YourViewName' in SSMS or Azure Data Studio.
  • Locate the COLLATE clause(s) in the SELECT statement or underlying table definitions.
  • Run the following query to check current collations on involved columns:
  SELECT name, collation_name 
  FROM sys.columns 
  WHERE object_id = OBJECT_ID('YourTableName');
  • Confirm if the collation name ends in flags like _CI_AS without _BIN2, which indicates a non-deterministic Windows collation.

Step 2: Modify the View to Use a Deterministic Collation

  • Edit the view definition and explicitly cast the problematic column to a binary collation, such as Latin1_General_100_BIN2 or SQL_Latin1_General_CP1_CS_AS.
  • Example fix:
  ALTER VIEW YourSchema.YourViewName
  AS
  SELECT ColumnName COLLATE Latin1_General_100_BIN2 AS ColumnName
  FROM YourTableName;
  • Ensure every column used in GROUP BY, JOIN, or indexed expressions within the view uses a _BIN2 or BIN suffixed collation.

Step 3: Rebuild the Indexed View

  • Drop the existing clustered index on the view if a prior attempt partially succeeded:
  DROP INDEX IX_YourIndexName ON YourSchema.YourViewName;
  • Recreate the view using the updated, deterministic collation syntax from Step 2.
  • Attempt to create the unique clustered index again:
  CREATE UNIQUE CLUSTERED INDEX IX_YourIndexName 
  ON YourSchema.YourViewName (ColumnName);

Step 4: Validate Server and Database Default Collation Settings

  • Check the instance-level collation using:
  SELECT SERVERPROPERTY('Collation');
  • If the server was upgraded from an older Windows Server version (e.g., Windows Server 2019 to Windows Server 2025), verify that the OS locale settings have not altered the behavior of Windows collations.
  • Consider standardizing the database default collation to a _100_BIN2 version if multiple views and indexes are affected, using ALTER DATABASE cautiously in a maintenance window.

Alternative or Verification Steps

  • Verify the fix by re-running the CREATE INDEX statement on the view; successful execution with no Error 1961 confirms the resolution.
  • If modifying the view is not feasible due to application dependencies, create a persisted computed column on the base table with the binary collation applied, then reference that computed column in the view instead of casting collation directly within the view logic.