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

by

Troubleshooting Guide: Error 2011 – Index Hints Restricted to Memory-Optimized Tables

Why This Error Happens

This error occurs when a schema-bound object, such as a view, function, or natively compiled stored procedure, contains an index hint (e.g., WITH (INDEX(...))) applied to a disk-based (non-memory-optimized) table. SQL Server 2025 enforces this restriction because the query optimizer treats memory-optimized and disk-based tables differently, and schema-bound objects require a fixed, predictable execution plan that disk-based index hints cannot guarantee in this context. The error is a T-SQL syntax/design-validation failure rather than a service, permissions, or networking issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object and Statement

  • Open SQL Server Management Studio (SSMS) and locate the exact error line using the message details in the “Messages” tab.
  • Run the following query to find schema-bound objects referencing index hints:
  SELECT OBJECT_NAME(object_id), definition
  FROM sys.sql_modules
  WHERE definition LIKE '%INDEX(%' AND is_schema_bound = 1;
  • Confirm whether the table referenced in the index hint is disk-based by checking sys.tables for the is_memory_optimized flag.

Step 2: Remove or Replace the Invalid Index Hint

  • Edit the schema-bound object definition (view, function, or procedure) using ALTER VIEW, ALTER FUNCTION, or ALTER PROCEDURE.
  • Remove the WITH (INDEX(index_name)) or WITH (INDEX(index_id)) clause attached to the disk-based table.
  • If query performance tuning was the original intent, replace the hint with a query plan guide or an OPTION (RECOMPILE) hint instead, since these are supported on disk-based tables within schema-bound objects.

Step 3: Convert the Table to Memory-Optimized (If Hint Is Required)

  • If the index hint is business-critical, migrate the target table to a memory-optimized table using CREATE TABLE ... WITH (MEMORY_OPTIMIZED = ON).
  • Export existing data using the SQL Server Import/Export Wizard or bcp utility before recreating the table.
  • Re-point the schema-bound object to the new memory-optimized table and re-test the original index hint syntax.

Step 4: Recompile and Validate the Schema-Bound Object

  • Run sp_refreshsqlmodule 'object_name' to force SQL Server to revalidate the object definition.
  • Check the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for any residual compilation warnings.
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell if changes do not take effect immediately.

Alternative or Verification Steps

  • Verify the fix by executing SELECT * FROM sys.sql_modules WHERE definition LIKE '%INDEX(%' again — the offending disk-based table reference should no longer appear in schema-bound definitions.
  • As an alternative, if memory-optimized conversion is not feasible, rewrite the query logic to use a non-schema-bound wrapper object that calls the original table with the index hint outside the schema-bound context.