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

by

Troubleshooting Guide: Error 1959 – Cannot Create Index on View/Computed Column

Why This Error Happens

This error occurs because the database’s compatibility level is set below 80, which disables support for indexed views and indexes on computed columns. This typically happens when a database has been migrated or restored from a much older version of SQL Server without updating its compatibility settings. SQL Server enforces this restriction because these indexing features rely on engine behaviors introduced in SQL Server 2000 (compatibility level 80) and later.

Step-by-Step Resolution Guide

Step 1: Check the Current Compatibility Level

  • Open SQL Server Management Studio (SSMS) and connect to your SQL Server 2025 instance.
  • Run the following query to check the current setting:
  • SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • Confirm the value returned is less than 80 (e.g., 60 or 65 from legacy SQL Server 6.5/7.0 databases).

Step 2: Raise the Compatibility Level Using sp_dbcmptlevel or ALTER DATABASE

  • For modern syntax, execute the following T-SQL command:
  • ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;
  • Alternatively, on older driver support, use: EXEC sp_dbcmptlevel 'YourDatabaseName', 160;
  • Note: SQL Server 2025 supports compatibility levels up to 170; however, 160 (SQL Server 2022) is recommended if you need backward-compatible query behaviors for legacy applications.

Step 3: Validate Application Compatibility Before Finalizing

  • Since raising the compatibility level can change query optimizer behavior, test critical stored procedures and application queries in a staging environment first.
  • Check for deprecated features by querying sys.dm_db_persisted_sku_features or reviewing the SQL Server Error Log for new warnings.

Step 4: Re-run the Index Creation Script

  • Navigate back to your original T-SQL script that failed.
  • Execute the CREATE INDEX or CREATE UNIQUE CLUSTERED INDEX statement again on the view or computed column.
  • Ensure the SCHEMABINDING option is included in the view definition, as this is a prerequisite for indexed views separate from the compatibility level.

Step 5: Restart Dependent Services (If Necessary)

  • If the change does not take effect immediately in connected applications, restart the SQL Server (MSSQLSERVER) service via services.msc on your Windows Server 2025 host.
  • Clear the procedure cache using DBCC FREEPROCCACHE; to ensure no stale execution plans are referencing the old compatibility level.

Alternative or Verification Steps

  • Verify the Fix: Re-run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the value is now 130 or higher, then attempt the index creation again to ensure Error 1959 no longer appears.
  • Alternative Solution: If changing the compatibility level is not feasible due to legacy application dependencies, consider refactoring the query logic to avoid indexed views entirely, using standard non-clustered indexes on the underlying base tables instead.