SQL Server – Adding COMPRESSION_DELAY to COLUMNSTORE INDEX

columnstoresql serversql-server-2016

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().

Best Answer

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 <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]}  
}
Related Question