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

by

Troubleshooting Guide: SQL Server Error 3805 – Non-Deterministic Conversion in Computed Column Index

Why This Error Happens

This error occurs when an indexed computed column relies on an implicit conversion from a string data type (such as varchar or nvarchar) to a date or datetime type, and that conversion depends on session settings like LANGUAGE or DATEFORMAT. Because the result of the conversion can change based on the session context in which the index was built versus queried, SQL Server flags the index as potentially non-deterministic and therefore unreliable for maintaining data integrity. This typically surfaces after an in-place upgrade to SQL Server 2025 or after a database is moved to a server with different regional/language settings than the original.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Index and Computed Column

  • Run the following query to extract the exact object names from the error text:
  SELECT i.name AS IndexName, t.name AS TableName, c.name AS ColumnName, c.definition
  FROM sys.indexes i
  JOIN sys.tables t ON i.object_id = t.object_id
  JOIN sys.computed_columns c ON t.object_id = c.object_id
  WHERE t.name = 'YourTableName';
  • Confirm the column definition contains an implicit string-to-date cast (e.g., CAST(OrderDateString AS DATE)).

Step 2: Validate Index Integrity with DBCC CHECKTABLE

  • Open SQL Server Management Studio (SSMS) 21 or later on Windows 11 / Windows Server 2025.
  • Execute:
  DBCC CHECKTABLE ('YourTableName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Review output for actual corruption (Msg 8928 or 8951) versus just the deterministic warning. If corruption is found, proceed to restore from backup or run REPAIR_REBUILD in a maintenance window.

Step 3: Rewrite the Computed Column Definition with Explicit CONVERT

  • Drop the existing index on the affected computed column:
  DROP INDEX IX_YourIndexName ON dbo.YourTableName;
  • Drop and recreate the computed column using an explicit, deterministic style code (e.g., style 23 or 126 for ISO formats):
  ALTER TABLE dbo.YourTableName DROP COLUMN ComputedDateColumn;
  ALTER TABLE dbo.YourTableName
    ADD ComputedDateColumn AS CONVERT(date, OrderDateString, 23) PERSISTED;
  • Rebuild the index on the corrected, now-deterministic column:
  CREATE INDEX IX_YourIndexName ON dbo.YourTableName (ComputedDateColumn);

Step 4: Standardize Server-Level Language and Date Format Settings

  • Check current default language on Windows Server 2025 instance:
  EXEC sp_configure 'default language';
  • Ensure SET DATEFORMAT and SET LANGUAGE are consistent across all application connection strings and linked servers to prevent recurrence after future restores or migrations.

Step 5: Rebuild All Related Indexes and Update Statistics

  • Execute a full index rebuild on the table to clear any residual metadata flags:
  ALTER INDEX ALL ON dbo.YourTableName REBUILD;
  UPDATE STATISTICS dbo.YourTableName;
  • Confirm no further Error 3805 entries appear in the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Alternative or Verification Steps

  • Re-run DBCC CHECKTABLE ('YourTableName') WITH ALL_ERRORMSGS after rebuilding; a clean result with zero allocation/consistency errors confirms the fix.
  • If explicit CONVERT is not feasible due to application dependencies, as an alternative, mark the column as non-persisted (remove PERSISTED) so it is computed at query time rather than stored and indexed, eliminating the determinism requirement entirely.