# Creating a Columnstore Table

The default table type in SingleStore is columnstore. The default can be changed to rowstore by updating the `default_table_type` engine variable to rowstore. See [List of Engine Variables](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables.md) for more details.

Currently, SingleStore Helios supports one sort key per table; however, if you do not want to define a key (e.g. if records are already inserted in the right order or you do not care about the sort order), `SORT KEY()`.

> **📝 Note**: `KEY() USING CLUSTERED COLUMNSTORE` is a legacy syntax that is equivalent to `SORT KEY()`. SingleStore recommends using `SORT KEY()`.

Here is an example of a query that creates a columnstore table:

```sql
CREATE TABLE products (
     ProductId INT,
     Color VARCHAR(10),
     Price INT,
     Qty INT,
     SORT KEY (Price),
     SHARD KEY (ProductId)
);

```

As with any other SingleStore Helios table, we define a `SHARD KEY` to explicitly control the data distribution. We define it on `ProductId` since sharding on a high cardinality identifier column generally allows for a more even distribution and prevents skew. It is also possible to randomly distribute data by either omitting the shard key, or defining an empty shard key `SHARD KEY()`. You can have at most one shard key that is defined on zero or more columns.

Your columnstore table definition can contain metadata-only unenforced unique keys, single-column and multi-column hash keys (which may be UNIQUE or PRIMARY), and a FULLTEXT key.

For more information on creating columnstore tables that use hash keys, see [USING HASH behavior](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-table/#UUID-2d098a7e-1811-6223-8748-d20d3c8b53f6.md).

Besides the keys discussed so far in this section, you cannot add other keys to a columnstore table.

If a sort key is defined on a table, the rows of that table will be stored in a highly compressed columnar format on disk. Storage is optimized for efficient scan in the key order. Use of an empty key in the form of `SORT KEY()` is permitted and results in less expensive background maintenance of the columnstore table, without guaranteeing any particular order.

We describe the details of the format below. While SingleStore Helios can execute any query on a columnstore table that it can execute on a rowstore, some queries are more suitable for columnstore tables than others. Some queries that can benefit from using columnstore include:

* Queries that scan several columns out of a table with many columns. Columnstore table will only materialize columns that it actually needs to perform the query.
* Queries that scan a lot of data in sort key order. Scans in the columnstore key order are more CPU and memory efficient.
* Joins between two columnstore tables on the sort key. Such a join can be executed as a merge join, resulting in a good performance and low memory overhead.
* Queries with high selectivity filters on the sort key. By leveraging a technique called `segment elimination`, SingleStore Helios can open only those row segments that actually contain relevant rows, significantly improving the performance of queries with high selectivity.

Columnstore tables are also not constrained by the amount of available memory, unlike rowstore tables.

## Creating a Columnstore Table Using Standard CREATE TABLE Syntax

The columnstore as default feature allows you to create a columnstore table using standard `CREATE TABLE` syntax.

## The Benefit of Using the Columnstore for Transaction Processing

Although rowstores are well-suited for transaction processing, it can be costly to store large datasets in rowstores, as they store all data in RAM. Because columnstores are disk-based, it is more economical to store data in columnstores as opposed to rowstores.

The columnstore [continues to evolve to support transactional workloads](https://docs.singlestore.com/cloud/create-a-database/columnstore.md) that would have traditionally used the rowstore. At the same time, the columnstore provides the fastest query processing performance for analytical workloads, making it a good choice for a mix of both transactional and analytical requirements.

## Enabling Columnstore as Default

The `default_table_type` engine variable specifies whether `CREATE TABLE` creates a rowstore or columnstore table, by default.

> **📝 Note**: `default_table_type` is set to `columnstore` for workspaces created with engine version 7.3 or newer. For workspaces created with an engine version prior to 7.3, `default_table_type` remains at `rowstore`, even after the upgrade. You cannot change the value of `default_table_type`.

You can create a rowstore table with `CREATE ROWSTORE TABLE ...`, regardless of the setting of `default_table_type`.

## Conversion of Standard `CREATE TABLE` Syntax to Columnstore `CREATE TABLE` Syntax

When the `default_table_type` is `columnstore`, the query engine internally converts standard `CREATE TABLE` syntax to equivalent columnstore-specific `CREATE TABLE` syntax, as follows:

## `SORT KEY()`

The `SORT KEY()` clause is added, if you have not specified the clause explicitly. For example,

```sql
CREATE TABLE t(a INT, b INT);

```

is equivalent to:

```sql
CREATE TABLE t(a INT, b INT, SORT KEY());

```

## `KEY(...)`

## Single-Column Keys

`KEY(<column name>)` is equivalent to `KEY(<column name>) USING HASH`. For example,

```sql
CREATE TABLE t(a INT, b INT, KEY(a));

```

is equivalent to:

```sql
CREATE TABLE t(a INT, b INT, SORT KEY(), KEY(a) USING HASH);

```

## Multi-Column Keys

`KEY(<column 1 name>,<column 2 name>, ... <column n name>)` is equivalent to `KEY(<column 1 name>,<column 2 name>,... <column n name>) USING HASH`. For example,

```sql
CREATE TABLE t(a INT, b INT, c INT, KEY(a,b));

```

is equivalent to:

```sql
CREATE TABLE t(a INT, b INT, SORT KEY(), KEY(a,b) USING HASH);

```

Using multi-column unique keys in a columnstore table is supported starting in engine version 7.5.

A query against `t` with an equality filter on `a`, an equality filter on `b`, or equality filters on both `a` and `b` could benefit from `KEY(a,b) USING HASH`. A query that uses both equality filters would be the most efficient.

Unlike other database products, SingleStore utilizes per-segment inverted indexes and cross-segment indexes to support multi-column keys while minimizing storage costs.

Each secondary index contains a collection of multiple cross-segment indexes, each of which is a hash table covering one or more segments. The background merger (An algorithm used by SingleStore that allows columnstore segments to maintain sort order (as close as possible), while data is being ingested or updated.) works to merge the hash tables to reduce the number of lookups necessary for a seek operation.

These data structures work together to accomplish these fast seeks as follows:

* The inverted index is built to map values of an index column to a posting list that stores row offsets within the segment with the value searched. The posting list logs which segment a specific value is located and uses row offsets to determine the value is located.

  ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt8394be32540f2af9/6a2c42d3724dc329c181c685/diagram_per-segment-index-6uHqVx.png)
* The cross-segment indexes map values of each indexed column with the ID of the segment where the searched value is located. The cross-segment indexes also map to the starting location of the corresponding postings list in the inverted index for each segment. Lastly, for a multi-column index like the example above (`KEY(a,b) USING HASH)`), another collection of cross-segment indexes are built on the row of indexed columns to map from the hash of each tuple (`value_a` and `value_b`) to the starting locations of the corresponding per-segment postings lists for `value_a` and `value_b`.

  ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt997d6481b1d362d4/6a2c436fcace3a06327ba1e5/diagram_cross-segment-hash-tables-VlilGz.png)

## `UNIQUE KEY`

`UNIQUE KEY(<column name>)` is created as `UNIQUE KEY(<column name>) USING HASH`. For example,

```sql
CREATE TABLE t(a INT, b INT, SHARD(a), UNIQUE KEY(a));

```

is equivalent to:

```sql
CREATE TABLE t(a INT, b INT, SORT KEY(), SHARD(a), UNIQUE KEY(a) USING HASH);

```

> **📝 Note**: Columnstore tables do not support multiple unique keys. A unique key must contain all the columns of the shard key. The shard key cannot be empty in this case and can contain multiple columns.A columnstore table can either have a primary key or a unique key, but not both.

## `PRIMARY KEY`

A primary key can be defined for a columnstore table using the `PRIMARY KEY(column_name)` clause or the `PRIMARY KEY` keyword in the `CREATE TABLE` statement. For example:

```sql
CREATE TABLE t(a INT PRIMARY KEY, b INT);
```

```sql
CREATE TABLE t(a INT, b INT, PRIMARY KEY(a));
```

The index behavior is equivalent to defining a key as ``UNIQUE KEY `PRIMARY` (a) USING HASH``. However, semantically these definitions are not the same.&#x20;

> **📝 Note**: A primary key must contain all the column(s) of the shard key.A columnstore table can either have a primary key or a unique key, but not both.

## Considerations When Using Hash Keys and Sort Keys

> **⚠️ Warning**: The previous section explained that in columnstore as default mode, a standard `CREATE TABLE` statement using `KEY(<k>)` stores `<k>` as a hash key in the resulting columnstore table. Hash keys support fast equality lookups, but not fast range filters. If you want to use a range filter on a columnstore, you need to use a sort key `<sk>` by explicitly specifying `SORT KEY(<sk>)` in your `CREATE TABLE` statement.

## Viewing the Storage Structure of a Table

> **📝 Note**: After creating a columnstore table using standard `CREATE TABLE` syntax, run [SHOW CREATE TABLE](https://docs.singlestore.com/cloud/reference/sql-reference/show-commands/show-create-table.md) if you wish to see the table’s full, explicit definition of keys and sharding.

## Converting an Existing Rowstore Table to a Columnstore Table

You can convert an existing rowstore table to a columnstore table by creating a new columnstore table, copying the data from the rowstore table to the columnstore table, dropping the rowstore table, and renaming the columnstore table to the rowstore table. For example, suppose you have an existing rowstore table `t` with the columns `a INT` and `b INT`. You can convert this table to a columnstore table as follows, assuming `default_table_type` is set to `columnstore`:

```sql
CREATE TABLE t2(a INT, b INT) AS SELECT * FROM t;
DROP TABLE t;
ALTER TABLE t2 RENAME TO t;

```

## Example Database Schema that is Created with Columnstore as Default

Suppose you want to create a database schema `track_visits` that tracks page visits on a website. For each page (URL) that is visited, you store the host name and path of the URL. For example, for the URL `myexample.com/a/b/`, the host name is `myexample.com` and the path is `a/b`.

For each page that is visited, you store the date/time of the visit and the user who visited the page. You also store the account that each user belongs to.

The schema contains the tables `accounts`, `users`, `webpages` and `webpage_visits`. The definitions for these tables are provided in the [Standard Syntax for Creating the Tables in the Schema](https://docs.singlestore.com/cloud/create-a-database/creating-a-columnstore-table/#UUID-bd806520-e67c-53d2-f56f-f0392e5dafde.md) section.

## Use Case for Using Default as Columnstore to Create the `track_visits` Schema

## Analytical Processing

Columnstores (as compared to rowstores) are optimized for analytical processing. With this schema, you can more quickly answer analytical questions such as:

* How many visits occurred within a particular time period?
* On average, how many visits are there per user?
* On average, how many users are there per account?
* How many unique webpages were clicked?

## Transactional Processing

You can utilize the [Universal Storage](https://docs.singlestore.com/cloud/create-a-database/columnstore/universal-storage.md) capabilities to perform transaction processing operations on the tables in the schema. For example:

* To find all of the webpage visits containing a particular hostname, you can run a query such as `SELECT * FROM webpages WHERE url_host = 'myexample.com';`. This query allows you take advantage of the hash index on `url_host`, which allows the query to run faster than if the hash index was not used.
* To find all of the webpages visited on a particular date and time, you can run a query such as `SELECT * FROM webpage_visits v JOIN webpages w ON v.page_id = w.id WHERE v.when_clicked = '2020-01-01 16:35:00';` This query does a highly selective join, as there are only a few records in `webpage_visits` that match the equality filter on `when_clicked`.

## Standard `CREATE TABLE` Syntax for Creating the Tables in the `track_visits` Schema

Following are the `CREATE TABLE` statements for the tables in the `track_visits` schema. The statements use standard `CREATE TABLE` syntax. The statements are run in columnstore as default mode.

```sql
CREATE DATABASE track_visits;
USE track_visits;

CREATE REFERENCE TABLE accounts(
	id INT AUTO_INCREMENT,
	name TEXT,
	PRIMARY KEY(id),
	UNIQUE KEY(name)
);

CREATE TABLE users (
	id BIGINT AUTO_INCREMENT,
	user_name TEXT,
	account_id BIGINT,
	PRIMARY KEY (id)
);

CREATE TABLE webpages(
	id BIGINT AUTO_INCREMENT,
	url_host TEXT,
	url_path TEXT,
	PRIMARY KEY (id),
	KEY(url_host)
);

CREATE TABLE webpage_visits(
	id BIGINT AUTO_INCREMENT,
	user_id BIGINT,
	page_id INT,
	when_clicked DATETIME,
	PRIMARY KEY (id),
	KEY (user_id, page_id),
	KEY (when_clicked)
);
```

## Storage Structure of Tables in the `track_visits` Schema

The following definitions show the structure of the tables in the `track_visits` schema after they are stored as a result of running the [standard CREATE TABLE statements](https://docs.singlestore.com/cloud/create-a-database/creating-a-columnstore-table.md).

> **📝 Note**: `INT`, and `BIGINT`, which are used in the standard `CREATE TABLE` statements, are stored as `INT(11)` and `BIGINT(20)`, respectively.In some cases, `TEXT` is stored as `BLOB` (they are equivalent).This behavior is independent of the columnstore as default feature.

## `accounts`

```sql
CREATE REFERENCE TABLE accounts(
    id INT NOT NULL AUTO_INCREMENT,
    name TEXT,
    UNIQUE KEY `PRIMARY` (id) USING HASH,
    UNIQUE KEY(name) USING HASH,
    SORT KEY()
);

```

## `users`

```sql
CREATE TABLE users (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    user_name TEXT,
    account_id BIGINT,
    SHARD(id),
    UNIQUE KEY `PRIMARY` (id) USING HASH,
    SORT KEY()
);

```

## `webpages`

```sql
CREATE TABLE webpages(
    id BIGINT NOT NULL AUTO_INCREMENT,
    url_host BLOB,
    url_path BLOB,
    SHARD(id),
    UNIQUE KEY `PRIMARY` (id) USING HASH,
    KEY(url_host) USING HASH,
    SORT KEY()
);

```

## `webpage_visits`

```sql
CREATE TABLE webpage_visits(
	id BIGINT(20) NOT NULL AUTO_INCREMENT,
	user_id BIGINT(20),
	page_id INT(11),
	when_clicked DATETIME,
	SHARD(id),
	UNIQUE KEY `PRIMARY` (id) USING HASH,
	KEY(user_id, page_id) USING HASH,
	KEY (when_clicked) USING HASH,
	SORT KEY()
);
```

## Querying `webpage_visits` Using a Range Filter

If you intend to query `webpage_visits` using a range filter on `when_clicked`, you could have made `when_clicked` the sort key. You could have done this using the following standard `CREATE TABLE` syntax.

```sql
CREATE TABLE webpage_visits(
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT,
    page_id INT,
    when_clicked DATETIME,
    PRIMARY KEY (id),
    KEY (user_id, page_id),
    SORT KEY (when_clicked)
);

```

The query engine would internally convert the standard `CREATE TABLE` syntax to equivalent columnstore-specific `CREATE TABLE` syntax, as follows:

```sql
CREATE TABLE webpage_visits(
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT,
    page_id INT,
    when_clicked DATETIME,
    UNIQUE KEY `PRIMARY` (id) USING HASH,
    KEY (user_id) USING HASH,
    KEY (page_id) USING HASH,
    SORT KEY (when_clicked) 
);

```

***

Modified at: September 17, 2025

Source: [/cloud/create-a-database/creating-a-columnstore-table/](https://docs.singlestore.com/cloud/create-a-database/creating-a-columnstore-table/)

(An index of the documentation is available at /llms.txt)