4

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

1 Answer 1

7

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]}  
}  
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.