STORAGE.COLUMN_STORE

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

See also:

• Columnstore indexes: Overview (Microsoft documentation)

• PRIMARY_KEY

• Hybrid table support in Snowflake

Usage

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:

STORAGE.COLUMN_STORE: false

And Primary key defined for the entity:

Key definition:

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

DDL generated by Agile Data Engine:

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

Azure SQL Database: Enabling columnstore indexes

Option set to true:

STORAGE.COLUMN_STORE: true

DDL generated by Agile Data Engine:

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

Azure Synapse SQL: Creating a heap table

Option set to false:

STORAGE.COLUMN_STORE: false

DDL generated by Agile Data Engine:

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