patternsqlMinor

Adding COMPRESSION_DELAY to an existing COLUMNSTORE INDEX

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

columnstoreaddingcompression_delayexistingindex

Problem

When I create an COLUMNSTORE index I can specify COMPRESSION_DELAY = x [minutes] in the CREATE-Statement. It delays the movement of the rows in an closed segment from the delta store to the compressed columnstore storage. Reason to do so: your import process is not just a simple INSERT but has some UPDATEs and maybe DELETEs too.

Is there a way to set this value afterwards without recreating the whole index (which would take very long on our big table)?

I tested already REBUILD and REORGANIZE, but they both do not understand the COMPRESSION_DELAY option in the WITH().

Solution

An existing columnstore index can be altered as such:

ALTER INDEX ColumnstoreIndexName ON SchemaName.ObjectName
SET (COMPRESSION_DELAY  = x Minutes);

As found in the documentation on ALTER INDEX

ALTER INDEX { index_name | ALL } ON   
{  
...
    | SET (  [ ,...n ] ) 

::=  
{  
      ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF}
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | COMPRESSION_DELAY= {0 | delay [Minutes]}  
}

Code Snippets

ALTER INDEX ColumnstoreIndexName ON SchemaName.ObjectName
SET (COMPRESSION_DELAY  = x Minutes);

ALTER INDEX { index_name | ALL } ON <object>  
{  
...
    | SET ( <set_index_option> [ ,...n ] ) 


<set_index_option>::=  
{  
      ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF}
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | COMPRESSION_DELAY= {0 | delay [Minutes]}  
}

Context

StackExchange Database Administrators Q#262186, answer score: 7

Revisions (0)

No revisions yet.