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

by

Troubleshooting Guide: Error 5382 – Index on Expression Feature Disabled

Why This Error Happens

This error occurs because SQL Server 2025 introduces a new feature called “Index on Expression,” which allows indexes to be created on computed expressions rather than just physical columns. When this feature flag is turned OFF at the instance or database level, SQL Server blocks any operation—including DROP INDEX—on indexes that were built using expression-based columns, since the engine cannot safely parse or validate the expression metadata while the feature is disabled. This typically happens after a server migration, a configuration rollback, or when a database is restored onto an instance with a different feature configuration than the source.

Step-by-Step Resolution Guide

Step 1: Verify the Current Feature Configuration Status

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to the target instance.
  • Run the following query to check the current state of the feature:
  SELECT name, value, value_in_use 
  FROM sys.configurations 
  WHERE name = 'index on expression feature';
  • Confirm that value_in_use returns 0, which indicates the feature is currently disabled.

Step 2: Enable the ‘Index on Expression’ Feature

  • Execute the following T-SQL commands to turn the feature back on at the instance level:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'index on expression feature', 1;
  RECONFIGURE;
  • Restart the SQL Server service via Windows Services (services.msc) or SQL Server Configuration Manager to ensure the change propagates correctly on Windows Server 2025/Windows 11.

Step 3: Retry the DROP INDEX Operation

  • Reconnect to the database and attempt the original command again:
  DROP INDEX [YourIndexName] ON [dbo].[YourTableName];
  • If the command succeeds without Error 5382, the feature toggle was the root cause.

Step 4: Check Database-Level Compatibility Settings

  • If the error persists, verify the database compatibility level, as expression indexes require compatibility level 170 or higher:
  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the level is lower, update it using:
  ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

  • Verification: Query sys.indexes and sys.index_columns joined with sys.computed_columns to confirm the index no longer appears in the system catalog after the drop.
  • Alternative Fix: If enabling the feature is not permitted by your organization’s security policy, export the table schema, manually migrate data to a new table without the expression-based index, and rename the tables to swap them into production.