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

[andreasjordan]

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.