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

by

Troubleshooting Guide: SQL Server Error 10798 – Invalid Data Compression Setting

Why This Error Happens

This error occurs because the COLUMNSTORE or COLUMNSTORE_ARCHIVE data compression setting is being applied to a table, index, or partition type that does not support it, such as a standard rowstore clustered or nonclustered index. SQL Server 2025 strictly validates compression types against the underlying storage structure, and columnstore-specific compression can only be assigned to actual columnstore indexes. This typically happens when a script is copied from a columnstore table definition and reused on a traditional heap or B-tree index without updating the WITH (DATA_COMPRESSION = ...) clause.

Step-by-Step Resolution Guide

Step 1: Identify the Object and Index Type

  • Run sp_help 'YourTableName' or query sys.indexes to check the type_desc column for the object in question.
  • Confirm whether the index is CLUSTERED, NONCLUSTERED, CLUSTERED COLUMNSTORE, or NONCLUSTERED COLUMNSTORE.
  • Look specifically for any CREATE INDEX or ALTER TABLE statement that references COLUMNSTORE or COLUMNSTORE_ARCHIVE.

Step 2: Correct the Compression Syntax in T-SQL

  • If the object is a standard rowstore index, update the script to use a valid setting: NONE, ROW, or PAGE.
  • Example fix for a rowstore table:
  ALTER TABLE dbo.YourTable REBUILD WITH (DATA_COMPRESSION = PAGE);
  • If compression is not required, explicitly set it to NONE as suggested by the error message:
  ALTER TABLE dbo.YourTable REBUILD WITH (DATA_COMPRESSION = NONE);

Step 3: Validate Index Architecture via SSMS GUI

  • Open SQL Server Management Studio (SSMS) on your Windows Server 2025/Windows 11 machine.
  • Navigate to Object Explorer > Databases > [YourDB] > Tables > [YourTable] > Storage > Compression.
  • Ensure the “Compression Type” dropdown matches the actual index architecture (do not select Columnstore options for Rowstore tables).
  • Click Script to generate the corrected T-SQL and verify the syntax before execution.

Step 4: Rebuild the Index with Correct Partition Alignment

  • If the table is partitioned, ensure you specify compression settings for every partition individually, as a global setting can sometimes conflict with partition-specific columnstore remnants.
  • Use the following syntax to apply settings per partition number:
  ALTER INDEX ALL ON dbo.YourTable
  REBUILD PARTITION = ALL
  WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1));

Alternative or Verification Steps

  • Verification: Execute SELECT partition_number, data_compression_desc FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.YourTable'); to confirm the compression type now reads NONE, ROW, or PAGE successfully.
  • Alternative: If you specifically intended to use columnstore compression, drop the existing rowstore index and create a new NONCLUSTERED COLUMNSTORE INDEX instead of attempting to alter the existing B-tree structure.