Index.Rebuild() silently ignores DataCompression = None - the ALTER INDEX ... REBUILD is generated without DATA_COMPRESSION and the index stays compressed

Setting DataCompression = None on the physical partitions of a compressed index and calling Rebuild() does not decompress the index. The generated ALTER INDEX ... REBUILD statement contains no DATA_COMPRESSION clause at all, and SQL Server preserves the existing compression setting on a rebuild — so the call succeeds and silently does nothing. Setting any other compression type the same way works, and the single-partition overload Rebuild(partitionNumber) works for None too; only whole-index Rebuild() with None is affected.

Repro

# Index currently PAGE compressed
$index = $server.Databases["db"].Tables["t"].Indexes["ix"]
foreach ($p in $index.PhysicalPartitions) { $p.DataCompression = "None" }
$index.Rebuild()
# Generated: ALTER INDEX [ix] ON [dbo].[t] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, ...)
# Expected:  ... WITH (..., DATA_COMPRESSION = NONE)
# Result: no error, index still PAGE compressed

dbatools has worked around this for years in Set-DbaDbCompression by dropping to raw T-SQL for None only (Set-DbaDbCompression.ps1#L355-L373). The workaround comment links the original report on UserVoice (feedback.azure.com item 34080112), which is lost since that platform was retired — hence this re-file.

Mechanism

Rebuild() keeps optimizePartitionNumber = -1 and ends in scripter.GetRebuildScript(false, -1) (IndexBase.cs#L1093-L1102, #L1299-L1330).

In ScriptIndexRebuildOptions, the whole-index case (rebuildPartitionNumber == -1) reuses ScriptCompression — the same helper CREATE scripting uses (IndexScripter.cs#L1866-L1893). That helper calls GetCompressionCode(isOnAlter: false, isOnTable: false, sp) (IndexScripter.cs#L949-L965) — and GetCompressionCode only emits DATA_COMPRESSION = NONE when isOnAlter is true (PhysicalPartitionCollectionBase.cs#L560-L571):

if (isOnAlter && (noneCompressionCount > 0))
{
    if (noneCompressionCount == this.Count)
    {
        return string.Format(SmoApplication.DefaultCulture, "DATA_COMPRESSION = NONE");
    }
    ...

Omitting NONE is correct for CREATE (it is the default there), but on a REBUILD the omission means "keep what you have". The design already anticipates this distinction — IsCompressionCodeRequired(bool isOnAlter) returns isOnAlter when every partition is None, with the comment "If it's asked by alter method then have to generate in any case" (PhysicalPartitionCollectionBase.cs#L160-L181) — the rebuild path just never passes true.

The single-partition path is the proof by contrast: Rebuild(partitionNumber) uses the dirty-state check and the per-partition GetCompressionCode(partitionNumber), which returns DATA_COMPRESSION = NONE unconditionally (PhysicalPartitionCollectionBase.cs#L132-L146) — so rebuilding one partition to None works.

Table.Rebuild() shares this scripting helper, so the table-level equivalent is likely affected as well (not separately verified).

Suggested fix

In the rebuildPartitionNumber == -1 branch of ScriptIndexRebuildOptions, script compression with ALTER semantics instead of CREATE semantics — e.g. give ScriptCompression an isOnAlter parameter and pass true from the rebuild path (both for the IsCompressionCodeRequired gate and the GetCompressionCode call). That makes an all-None dirty collection emit DATA_COMPRESSION = NONE, matching what Rebuild(partitionNumber) already does.

Happy to submit a PR along those lines if that helps.

This was created by Claude and reviewed by Andreas Jordan.

Bắt đầu trong IndexBase.cs, tại khu vực quanh Rebuild() và ScriptIndexRebuildOptions, sau đó so sánh đường xử lý cho toàn bộ index với đường xử lý đang hoạt động cho một partition đơn. Đọc các phần quanh ScriptCompression, GetCompressionCode và IsCompressionCodeRequired trong IndexScripter.cs và PhysicalPartitionCollectionBase.cs. Tái hiện ví dụ PowerShell trên SQL Server; hoàn tất khi Rebuild() của toàn bộ index phát ra DATA_COMPRESSION = NONE và loại bỏ tính năng nén.