STORAGE.COLUMN_STORE

Target database: Azure SQL Database, Azure Synapse SQL, Snowflake

STORAGE.COLUMN_STORE enables/disables columnstore indexes on Azure SQL Database and on Azure Synapse SQL.

See also:

Usage

OPTION

STORAGE.COLUMN_STORE: Boolean

Default: false

Notes

Columnstore can be enabled for tables in Azure SQL Database and Snowflake.

With Azure Synapse SQL, leaving this physical option unset results in a columnstore table which is the default. To create a heap table, explicitly set STORAGE.COLUMN_STORE to false (example below). See Azure Synapse documentation for more information on indexing tables.

With Snowflake, setting this explicitly to false enables will enable creation of HYBRID TABLE, but also primary key definition is required to be set.

Examples

Snowflake: Enabling hybrid table

Option set to false:

OPTION

STORAGE.COLUMN_STORE: false

And Primary key defined for the entity:

Key definition:

CODE

KEY NAME: PK_DATA
KEY TYPE: PRIMARY_KEY
ATTRIBUTE 1: data_id

DDL generated by Agile Data Engine:

SQL

CREATE HYBRID TABLE ... (
    ...
    CONSTRAINT PK_DATA PRIMARY KEY ( data_id )
);

Azure SQL Database: Enabling columnstore indexes

Option set to true:

OPTION

STORAGE.COLUMN_STORE: true

DDL generated by Agile Data Engine:

SQL

CREATE TABLE ... (
    ...
    INDEX ... CLUSTERED COLUMNSTORE
);

Azure Synapse SQL: Creating a heap table

Option set to false:

OPTION

STORAGE.COLUMN_STORE: false

DDL generated by Agile Data Engine:

SQL

CREATE TABLE ... (
    ...
)
WITH (HEAP);