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

by

Troubleshooting Guide: Error 10789 in SQL Server 2025

Why This Error Happens

This error occurs when creating or altering a memory-optimized table with a hash index but the BUCKET_COUNT option is omitted from the index definition. SQL Server’s In-Memory OLTP engine requires an explicit bucket count for hash indexes because it pre-allocates a fixed hash table structure, unlike nonclustered (range) indexes which size dynamically. This is purely a syntax/configuration omission rather than a permissions or corruption issue, and it will consistently block DDL execution until corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Index Definition

  • Locate the CREATE TABLE or CREATE INDEX statement referenced in the error message, including the table and index name shown in the %.*ls placeholders.
  • Open the script in SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
  • Confirm the index is defined with WITH (BUCKET_COUNT = N) missing from its INDEX clause.

Step 2: Add the Required BUCKET_COUNT Option

  • Edit the index definition to include BUCKET_COUNT, for example:
CREATE TABLE dbo.MyTable
(
    Id INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    INDEX ix_Id HASH (Id) WITH (BUCKET_COUNT = 1048576)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
  • Set BUCKET_COUNT to roughly 1-2x the expected number of unique index key values (rounded up to the nearest power of two for optimal hash distribution).

Step 3: Recreate or Alter the Table/Index

  • If the table already exists, drop and recreate the memory-optimized table, since hash index bucket counts cannot be altered in-place prior to certain compatibility levels.
  • Alternatively, use ALTER TABLE ... ALTER INDEX ... REBUILD WITH (BUCKET_COUNT = N) if your SQL Server 2025 build supports online bucket count changes.
  • Re-run the script and confirm the DDL statement completes without raising Error 10789.

Step 4: Validate Memory-Optimized Filegroup Configuration

  • Ensure the database has a MEMORY_OPTIMIZED_DATA filegroup properly configured under C:\SQLData\MemoryOptimized\ (or your chosen path) on Windows Server 2025/Windows 11.
  • Run sp_helpfilegroup or query sys.filegroups to confirm the filegroup exists before attempting to create memory-optimized tables with hash indexes.

Alternative or Verification Steps

  • Verify the fix by querying sys.hash_indexes and checking the bucket_count column matches your intended value for the affected table.
  • If the error persists after adding BUCKET_COUNT, check compatibility level with SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB' and ensure it’s set to 150 or higher to support full In-Memory OLTP hash index syntax.