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

by

Troubleshooting Guide: Error 12328 — Indexes on Character Columns Require *_BIN2 Collation

Why This Error Happens

This error occurs because SQL Server 2025 requires binary (*BIN2) collations for certain index types, such as indexes supporting memory-optimized tables, temporal tables with specific features, or columnstore-related operations, in order to guarantee deterministic byte-level comparisons. When a character column (char, varchar, nchar, nvarchar) uses a non-binary collation (e.g., SQLLatin1GeneralCP1 or any case-insensitive/accent-insensitive collation), the comparison logic becomes non-deterministic and incompatible with the index structure being created. This is most commonly triggered during the creation of indexes on memory-optimized tables, natively compiled stored procedures, or certain hash/range index types that mandate deterministic binary ordering.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Current Collation

  • Run the following query to find the column and its collation:
SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    c.collation_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
  AND t.name = 'YourTableName';
  • Confirm whether the collation ends in _BIN2. If not, this is the root cause of Error 12328.

Step 2: Alter the Column to Use a *_BIN2 Collation

  • Modify the column definition to explicitly use a binary collation compatible with your database’s language requirements:
ALTER TABLE dbo.YourTableName
ALTER COLUMN YourColumnName NVARCHAR(100) 
COLLATE Latin1_General_100_BIN2 NOT NULL;
  • Choose a _BIN2 collation that matches your existing locale (e.g., Latin1_General_100_BIN2, Japanese_100_BIN2) to avoid unwanted character ordering changes elsewhere in the application.
  • Note: This operation may require dropping dependent indexes or constraints first.

Step 3: Drop and Recreate the Index

  • Once the collation is corrected, drop the existing failed index (if partially created) and recreate it:
DROP INDEX IF EXISTS IX_YourIndexName ON dbo.YourTableName;

CREATE INDEX IX_YourIndexName 
ON dbo.YourTableName (YourColumnName);
  • For memory-optimized tables, ensure you recreate the table with MEMORY_OPTIMIZED = ON and the corrected collation applied at table creation time, since altering collations post-creation is not supported for memory-optimized tables.

Step 4: Rebuild Dependent Objects

  • Recompile any natively compiled stored procedures or views that reference the altered column:
EXEC sp_recompile 'dbo.YourStoredProcedureName';
  • Verify foreign keys, computed columns, or check constraints referencing the modified column still function correctly after the collation change.

Alternative or Verification Steps

  • Verify the fix by re-running the original CREATE INDEX statement — it should complete without raising Error 12328.
  • If altering the collation is not feasible due to application dependencies, use a computed persisted column with the _BIN2 collation applied only to that computed column, and build the index on the computed column instead of the original one.