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

by

Troubleshooting Guide: SQL Server Error 10791 – “Hash Indexes Are Permitted Only in Memory Optimized Tables”

Why This Error Happens

This error occurs when a CREATE TABLE or CREATE INDEX statement attempts to define a HASH index on a standard, disk-based table rather than a memory-optimized table. Hash indexes are a feature exclusive to the In-Memory OLTP engine and rely on a bucket-count structure that only memory-optimized tables support. This typically happens due to a scripting error, a copy-pasted template, or a misunderstanding of the table’s MEMORY_OPTIMIZED property during schema migration or deployment.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table Definition

  • Open the script or stored procedure that triggered Error 10791 in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
  • Locate the CREATE TABLE statement containing the INDEX ... HASH WITH (BUCKET_COUNT = N) clause.
  • Confirm whether the table was intended to be disk-based or memory-optimized by checking the original design documentation or schema diagram.

Step 2: Convert the Table to Memory-Optimized (If Hash Index Is Required)

  • Add the MEMORY_OPTIMIZED = ON clause to the table definition, along with a required DURABILITY setting.
  • Ensure a memory-optimized filegroup exists first by running:
  ALTER DATABASE [YourDatabase] ADD FILEGROUP [YourDB_MOD] CONTAINS MEMORY_OPTIMIZED_DATA;
  ALTER DATABASE [YourDatabase] ADD FILE (NAME='YourDB_MOD_File', FILENAME='C:\SQLData\YourDB_MOD') TO FILEGROUP [YourDB_MOD];
  • Rewrite the table creation script, for example:
  CREATE TABLE dbo.YourTable
  (
      Id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
      Data NVARCHAR(100)
  ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Step 3: Remove the Hash Index (If Memory-Optimization Is Not Needed)

  • If the table must remain disk-based, remove the HASH WITH (BUCKET_COUNT = N) syntax entirely.
  • Replace it with a standard clustered or nonclustered B-tree index instead:
  CREATE NONCLUSTERED INDEX IX_YourTable_Id ON dbo.YourTable (Id);
  • Re-run the modified script in SSMS or via sqlcmd on Windows Server 2025 to confirm the table is created without error.

Step 4: Validate File Paths and Permissions for Memory-Optimized Filegroups

  • If converting to memory-optimized storage, verify that the service account running SQL Server has write access to the directory specified in the FILENAME parameter (e.g., C:\SQLData\).
  • Check Windows Security permissions via File Explorer > Right-click folder > Properties > Security tab, ensuring the SQL Server service account has Full Control.
  • Restart the SQL Server service via services.msc if filegroup changes do not take effect immediately.

Alternative or Verification Steps

  • Run SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTable'; to confirm the table’s memory-optimized status returns 1 before applying hash indexes again.
  • If BUCKET_COUNT sizing is uncertain, use a nonclustered (B-tree) index on the memory-optimized table instead of HASH, as it auto-manages range scans without requiring a fixed bucket count.