# CREATE TABLE

The `CREATE TABLE` command creates a new table.

As of version 7.3 of SingleStore, Columnstore is the default table storage format. Columnstore is also known as **Universal Storage**. See the [Columnstore](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore.md) section for more information.

## Syntax

```sql
CREATE [ROWSTORE] [REFERENCE | TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] <table_name>
    (<create_definition>,...)
    [<table_options>]
    [[AS] SELECT ...]

CREATE TABLE [IF NOT EXISTS] new_tbl_name
    { LIKE original_tbl_name | (LIKE original_tbl_name) }
    [WITH DEEP | SHALLOW COPY]

<create_definition>:
    <column_name> { <column_definition> | AS <computed_column_definition> }
  | [CONSTRAINT [symbol]] PRIMARY KEY [<index_type>] (<index_column_name>,...)
      [<index_option>] ...
  | { INDEX | KEY } [<index_name>] [<index_type>] (<index_col_name>,...)
      [<index_option>] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [<index_name>] [<index_type>] (<index_column_name>,...)
      [<index_option>] ...
  | [CONSTRAINT [symbol]] SHARD KEY [<index_type>] (<index_column_name>,...) [METADATA_ONLY]
      [<index_option>] ...
  | SORT KEY (<index_column_name>,... [DESC])
  | COLUMN GROUP [column_group_name] ( * )
  | FULLTEXT[USING VERSION 1][<index_name>] (<index_column_name>,...)
  | FULLTEXT USING VERSION 2 [<index_name>] (<index_column_name>,...)
  | VECTOR {INDEX|KEY} [<index_name>] (<column>) [INDEX_OPTIONS '<json>']
  | MULTI VALUE INDEX(col1) INDEX_OPTIONS='<options>' 
  | FOREIGN KEY [foreign_key_name](col1,..., coln) REFERENCES table_referenced (col_referenced))

<column_definition>:
    <data_type> [NOT NULL | NULL] [DEFAULT <default_value>] [ON UPDATE <update_value>]
      [AUTO_INCREMENT [AS SEQUENCE]] [UNIQUE [KEY] | [PRIMARY] KEY] [SPARSE] [SERIES TIMESTAMP]

<computed_column_definition>:
    computed_column_expression PERSISTED [data_type | AUTO]

<data_type>:
    BIT[(<length>)]
  | TINYINT[(<length>)] [UNSIGNED]
  | SMALLINT[(<length>)] [UNSIGNED]
  | INT[(<length>)] [UNSIGNED]
  | INTEGER[(<length>)] [UNSIGNED]
  | BIGINT[(<length>)] [UNSIGNED]
  | REAL[(<length>,<decimals>)] [UNSIGNED]
  | DOUBLE[(<length>,<decimals>)] [UNSIGNED]
  | DECIMAL[(<length>[,<decimals>])] [UNSIGNED]
  | NUMERIC[(<length>[,<decimals>])] [UNSIGNED]
  | DATETIME
  | DATETIME(6)
  | TIMESTAMP
  | TIMESTAMP(6)
  | DATE
  | TIME
  | CHAR[(<length>)]
      [CHARACTER SET <character_set_name>] [COLLATE <collation_name>]
  | VARCHAR(<length>)
      [CHARACTER SET <character_set_name>] [COLLATE <collation_name>]
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
  | TEXT [BINARY]
  | MEDIUMTEXT [BINARY]
  | LONGTEXT [BINARY]
  | ENUM(<value1>,<value2>,<value3>,...)
  | SET(<value1>,<value2>,<value3>,...)
  | JSON [COLLATE <collation_name>]
  | GEOGRAPHY
  | GEOGRAPHYPOINT

<index_column_name>:
    <column_name> [(<length>)] [ASC | DESC]

<index_type>:
   | USING { BTREE | HASH }

<index_option>:
    KEY_BLOCK_SIZE [=] <value>
  | <index_type>
  | COMMENT '<string>'
  | BUCKET_COUNT [=] <value>
  | WITH (<index_kv_options>)
  | UNENFORCED [RELY | NORELY]

<index_kv_options>:
    <index_kv_option> [, <index_kv_option>] ...

<index_kv_option>:
    RESOLUTION = <value>
  | COLUMNSTORE_SEGMENT_ROWS = <value>
  | COLUMNSTORE_FLUSH_BYTES = <value>

<table_options>:
    <table_option> [[,] <table_option>] ...

<table_option>:
    AUTO_INCREMENT [=] <value>
  | COMMENT [=] '<string>'
  | AUTOSTATS_ENABLED = { TRUE | FALSE }
  | AUTOSTATS_CARDINALITY_MODE = {INCREMENTAL|PERIODIC|OFF}
  | AUTOSTATS_HISTOGRAM_MODE = {CREATE|UPDATE|OFF}
  | AUTOSTATS_SAMPLING = {ON|OFF}
  | COMPRESSION = SPARSE
```

## CREATE { TABLE | TABLES } AS INFER PIPELINE Syntax

```sql
CREATE TABLE [IF NOT EXISTS] <table_name> 
  AS INFER PIPELINE AS LOAD DATA 
  { LINK <link_name> |
    MONGODB "<collection>" CONFIG '<config_json>' CREDENTIALS '<credentials_json>' |
    MYSQL "<source_db>.<source_table>" CONFIG '<config_json>' CREDENTIALS '<credentials_json>' }
  FORMAT AVRO;

```

```sql
CREATE TABLES [IF NOT EXISTS] 
  AS INFER PIPELINE AS LOAD DATA 
  { LINK <link_name> |
    MONGODB '*' CONFIG '<conf_json>' CREDENTIALS '<cred_json>' |
    MYSQL "*" CONFIG '<config_json>' CREDENTIALS '<credentials_json>' }
  FORMAT AVRO;

```

SingleStore supports replicating data via Change Data Capture (CDC) pipelines using the `CREATE { TABLE | TABLES } AS INFER PIPELINE` syntax only from MongoDB® and MySQL data sources. Refer to the following for replicating data from the respective data source:

* [Replicate Data from MySQL](https://docs.singlestore.com/db/v9.1/load-data/data-sources/replicate-data-from-mysql.md)
* [Replicate Data from MongoDB®](https://docs.singlestore.com/db/v9.1/load-data/data-sources/replicate-data-from-mongodb.md)

> **📝 Note**: The `CREATE {TABLE|TABLES} ... AS INFER PIPELINE` statement only supports links (`LINK` clause) to the MySQL and MongoDB® data sources.

## Remarks

> **📝 Note**: Unless `CREATE ROWSTORE TABLE ...` or `SORT KEY()` are specified, the value of the `default_table_type` engine variable determines the type of table (`columnstore` or `rowstore`) that is created.When `default_table_type` is set to `columnstore`, you can [create a columnstore table using standard CREATE TABLE syntax](https://docs.singlestore.com/db/v9.1/create-a-database/creating-a-columnstore-table.md).`default_table_type` is set to `columnstore` by default.The setting of `default_table_type` applies to temporary tables. When creating `GLOBAL TEMPORARY` tables, if default\_table\_type is set to columnstore, you must use `CREATE ROWSTORE GLOBAL TEMPORARY TABLE`. `GLOBAL TEMPORARY` is not supported on columnstore tables.

* For more information about the data types listed above, and for an explanation of `UNSIGNED`, refer to the [Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) topic.
* The `SET` data type restricts the values that can be inserted for a table column. Only the set of strings that are listed for a column at the time of table creation can be inserted.
* `<table_name>` is the name of the table to create in the SingleStore database.
* The following note applies when the engine variable `table_name_case_sensitivity` is set to `OFF`: After you create a table, you cannot create another table having the same table name with a different case. Refer to the [Database Object Case Sensitivity](https://docs.singlestore.com/db/v9.1/reference/sql-reference/database-object-case-sensitivity.md) topic for more information.
* `CREATE TABLE` is slower in SingleStore than in MySQL. See [Code Generation](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation.md) for more information.
* The `MULTI VALUE INDEX` clause is only supported for `BSON` and `JSON` type columns. Refer to [Multi-Value Hash Index (BSON)](https://docs.singlestore.com/db/v9.1/reference/sql-reference/bson-functions/multi-value-hash-index-bson.md) or [Multi-Value Hash Index (JSON)](https://docs.singlestore.com/db/v9.1/create-a-database/multi-value-hash-index-json.md) for creating multi-value indexes on the respective column type.
* The `KEY` syntax is equivalent to using `INDEX` syntax when used in `CREATE TABLE` . The convention is to use the `KEY` syntax. `INDEX` syntax is generally used when creating an index on an existing table. See [CREATE INDEX](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-index.md) for more information.
* Foreign key referential integrity enforcement is not supported by SingleStore, but `FOREIGN KEY` syntax can be supported in SingleStore by setting the `ignore_foreign_keys` engine variable to `ON`. The default value for `ignore_foreign_keys` is `OFF`. See the [Specifying Unenforced Unique Constraints](https://docs.singlestore.com/db/v9.1/create-a-database/specifying-unenforced-unique-constraints/#section-idm253390805808688.md) page for more information.
* The `BTREE` index type creates a skip list index in SingleStore. This index has very similar characteristics to a BTREE index.
* If you do not want to specify a column (or columns) to sort on, or do not care about the sort order for your data, you can specify an empty key (e.g. `SORT KEY()`).
* The `SORT KEY()` order can be specified as ascending (`SORT KEY(index_column_name)`) or descending (`SORT KEY(index_column_name DESC)`). SingleStore does not support scanning a `SORT KEY()` in reverse order to its sort order:
  ```sql
  CREATE TABLE ct_sort (col1 int, SORT KEY(col1 DESC));

  ```
  ```sql
  EXPLAIN SELECT * FROM ct_sort ORDER BY col1 DESC;

  ```
  ```output

  +-------------------------------------------------------------------------------------------+
  | EXPLAIN                                                                                   |
  +-------------------------------------------------------------------------------------------+
  | Project [remote_0.col1]                                                                  
  | TopSort limit:[@@SESSION.`sql_select_limit`] [remote_0.col1 DESC]                         |
  | Gather partitions:all alias:remote_0 parallelism_level:sub_partition                      |
  | Project [t1.col1]                                                                         |
  | Top limit:[?]                                                                             |
  | ColumnStoreFilter [<after per-thread scan begin> AND <before per-thread scan end>]        |
  | OrderedColumnStoreScan test1.t1, SORT KEY col1 (col1 DESC) table_type:sharded_columnstore |
  +-------------------------------------------------------------------------------------------+

  ```
  ```sql
  EXPLAIN SELECT * FROM ct_sort ORDER BY col1;

  ```
  ```output

  +------------------------------------------------------------------------------------+
  | EXPLAIN                                                                            |
  +------------------------------------------------------------------------------------+
  | Project [remote_0.col1]                                                            |
  | TopSort limit:[@@SESSION.`sql_select_limit`] [remote_0.col1]                       |
  | Gather partitions:all alias:remote_0 parallelism_level:segment                     |
  | Project [t1.col1]                                                                  |
  | TopSort limit:[?] [t1.col1]                                                        |
  | ColumnStoreScan test1.t1, SORT KEY col1 (col1 DESC) table_type:sharded_columnstore |
  +------------------------------------------------------------------------------------+

  ```
* `SORT KEY()` is not allowed when using `CREATE ROWSTORE TABLE ...`.
* `KEY() USING CLUSTERED COLUMNSTORE` is a legacy syntax that is equivalent to `SORT KEY()`. SingleStore recommends using `SORT KEY()`.
* `BUCKET_COUNT` is specific to the `HASH` index type. It controls the bucket count of the hash table. It applies to rowstore hash indexes only and does not effect columnstore hash indexes.
* The `UNENFORCED` index option can be used on a `UNIQUE` constraint to specify that the unique constraint is unenforced. See [Unenforced Unique Constraints](https://docs.singlestore.com/db/v9.1/create-a-database/specifying-unenforced-unique-constraints.md).
* `RESOLUTION` is specific to index on geospatial columns. See [Working with Geospatial Features](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-geospatial-features.md) for more information.
* `COLUMNSTORE_SEGMENT_ROWS`, `COLUMNSTORE_FLUSH_BYTES` controls configuration variables specific to columnstore tables. See [Advanced Columnstore Configuration Options](https://docs.singlestore.com/db/v9.1/create-a-database/configuring-the-columnstore-to-work-effectively.md)) for more information.
* SingleStore supports `binary`, `utf8`, and `utf8mb4` character sets. The `COLLATE` clause in the `CREATE TABLE` statement can be used to override the server character set and collation, which are used as default values for the table.

  For more information, see [Character Set and Collation Override](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding/character-set-and-collation-override/#section-idm4550248527876832485681353744.md).
* `AUTOSTATS_ENABLED` controls if automatic statistics should be collected on this table. There are three categories of autostats - `AUTOSTATS_CARDINALITY_MODE`, `AUTOSTATS_HISTOGRAM_MODE`, and `AUTOSTATS_SAMPLING`. SingleStore allows you to independently control how each category of statistics is automatically gathered. Multiple autostats settings can be combined in a single `CREATE TABLE` statement. See [Automatic Statistics](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts.md) for more information.
* This command can be run on the master aggregator node, or a child aggregator node (see [Node Requirements for SingleStore Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md) ).
* This command causes implicit commits. Refer to [COMMIT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information.
* Specify the `AUTO` option instead of the `<data_type>` in the `<computed_column_definition>` clause to automatically infer the data type of the column from the `<computed_column_expression>`.
  > **📝 Note**: When using `CREATE TABLE` to define a persistent computed column, do not rely on `AUTO NOT NULL` to enforce non-nullability. For persistent computed columns, `AUTO` can cause the `NOT NULL` constraint to be ignored.To ensure that the computed column is non-nullable, define the computed column with an explicit type rather than `AUTO`. For more information, refer to [Persistent Computed Columns](https://docs.singlestore.com/db/v9.1/create-a-database/using-persistent-computed-columns.md).
* `<computed_column_expression>` defines the value of a computed column using other columns in the table, constants, built-in functions, operators, and combinations thereof. For more information see [Persistent Computed Columns](https://docs.singlestore.com/db/v9.1/create-a-database/using-persistent-computed-columns.md).
* Temporary tables, created with the `TEMPORARY` option, will be deleted when the client session terminates. For ODBC/JDBC, this is when the connection closes. For interactive client sessions, it is when the user terminates the client program.
* Global temporary tables, created with the `GLOBAL TEMPORARY` option, exist beyond the duration of a client session. If failover occurs, the global temporary tables lose data and enter an errored state; they need to be dropped and recreated. This command can be run only on the master aggregator. See [Global Temporary Tables](https://docs.singlestore.com/db/v9.1/create-a-database/other-schema-concepts.md) for details.
* The `SERIES TIMESTAMP` clause specifies a table column as the default column defining time order for implicit use by time series functions. This setting can be specified only for a single table column. The column can be one of the following data types: `DATE`,`TIME`,`DATETIME`, `DATETIME(6)`, `TIMESTAMP` or `TIMESTAMP(6)`. SingleStore recommends to use either of the `DATETIME` or `DATETIME(6)` types instead of one of the `TIMESTAMP` types because the automatic update behavior of `TIMESTAMP` is subject to change. See [Timestamp Behavior](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/time-and-date.md) for details.
* The `SERIES TIMESTAMP` clause does not affect the data type of a table column, rather it specifies the behavior of the column in the time-series-specific functions like `FIRST()`, `LAST()`, and `TIME_BUCKET()`.
* Keyless sharding distributes data across partitions uniformly at random but with the limitation that it does not allow single partition queries or local joins since rows are not assigned to specific partitions. Keyless sharding is the default for tables that do not have primary key or explicit shard key. You can explicitly declare a table as keyless sharded by specifying a [shard key](https://docs.singlestore.com/db/v9.1/introduction/distributed-architecture/sharding.md) with an empty list of columns in the `SHARD KEY()` constraint in the table definition.
* The `METADATA_ONLY` option on the `SHARD KEY` syntax prevents an index being created on the shard key. It will decrease overall memory usage. It can cause queries to run slower. It can only be used when creating your table.
* The optional `COLUMN GROUP` clause creates a materialized copy of each row as a separate index. It is supported on columnstore tables only. This structure can be used to accelerate full-row retrievals and updates for wide columnstore tables.

  Column group indexes use less RAM than rowstore tables which can reduce operation costs. Using column group indexes on columnstores can allow you to get both fast lookups and fast analytics on the same table. The column group index improves the performance of lookups, and the standard columnar representation is available to give fast analytics. Using a column group index on a columnstore table is easier to manage than having to move data between rowstore and columnstore tables.

  To find the size of a column group index, refer to the [How the Columnstore Works](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore/how-the-columnstore-works.md) page.

  The `column_group_name` argument is optional. If a column group name is not specified when creating a table, one is chosen by the engine. Using `COLUMN GROUP [column_group_name] (*)` creates a column group index on all columns in the table. Column group indexes created on a subset of table columns are not supported.

  The following is an syntax example of a columnstore table that creates a column group on all columns:
  ```sql
  CREATE TABLE col_group_1(id BIGINT, col1 VARCHAR(10), col2 VARCHAR(10), ..., coln INT, COLUMN GROUP col_gp_inx (*));
  ```
* The `WITH DEEP COPY` argument of the `CREATE TABLE new_tbl_name LIKE original_tbl_name` statement copies an existing table (`original_tbl_name`) and creates a new table that will have the same definition as the original table - including all of the data and metadata (such as indexes) in the original table. Users must have `SELECT` permissions to be able to execute SQL statements against the new table. Computed columns will be recomputed during the `WITH DEEP COPY` process.
* The `WITH SHALLOW COPY` argument of the `CREATE TABLE new_tbl_name LIKE original_tbl_name` statement copies an existing table (`original_tbl_name`) and creates a new table that will have the same definition as the original table. The data is not physically copied to the new table, but referenced against the original table. So any `SELECT` query made against either table produces the same result, until one of them is updated. Users must have `SELECT` permissions to be able to execute SQL statements against the new table.
* Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.

## MySQL Compatibility

SingleStore’s syntax differs from MySQL mainly in the data types and storage it supports, and some specific index hints.

* `KEY_BLOCK_SIZE [=] <value>` : value is currently ignored.

## DEFAULT Behavior

If `DEFAULT <default_value>` is specified in `<column_definition>`, and no value is inserted in the column, then `<default_value>` will be placed in the column during an `INSERT` operation.

## ON UPDATE Behavior

If `ON UPDATE <update_value>` is specified in `<column_definition>` and if any other column is updated but the specified column is not explicitly updated, then `update_value` will be placed in the column during an `UPDATE` operation. If the column is of the type `TIMESTAMP`, `TIMESTAMP(6)`, `DATETIME` , or `DATETIME(6)`, then you can update `<update_value>` to one of the following values: `CURRENT_TIMESTAMP()`, `CURRENT_TIMESTAMP(6)`, `NOW()`, or `NOW(6)`.

`ON UPDATE` can be used with these `TIMESTAMP/DATETIME[(6)]` types only, and you can only use one of the time functions as the argument. For more information, refer to [Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/time-and-date.md).

Note that if an `ON UPDATE` or `DEFAULT` clause is defined as `DATETIME` or `DATETIME(6)` and its precision does not match the precision of the column data type, SingleStore issues a warning during table creation. The operation still proceeds and SingleStore creates the table by adjusting the data type of the `ON UPDATE` or `DEFAULT` clause to match the column data type.

For example, consider the following `CREATE TABLE` statement:

```sql
CREATE TABLE example (
    col1 DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(),
    col2 DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    col3 DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
    col4 DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(6)
);

```

```output

Query OK, 0 rows affected, 2 warnings
```

Because of data type mismatch between the `DEFAULT` and `ON UPDATE` clauses and the respective `col1` and `col4` column data types, this command returns warnings. Run the `SHOW WARNINGS` command to view the warnings (output is formatted for readability):

```sql
SHOW WARNINGS\G

```

```output

*** 1. row ***
  Level: Warning
   Code: 1706
Message: Feature 'DATETIME type with conflicting scale' is not supported 
by SingleStore. Execution will continue, but the DEFAULT and/or ON UPDATE 
timestamp scale will match the declared value of column 'col1'. To avoid 
this warning, change the scale of your DEFAULT and/or ON UPDATE expression 
to match the declared column type.
*** 2. row ***
  Level: Warning
   Code: 1706
Message: Feature 'DATETIME type with conflicting scale' is not supported 
by SingleStore. Execution will continue, but the DEFAULT and/or ON UPDATE 
timestamp scale will match the declared value of column 'col4'. To avoid 
this warning, change the scale of your DEFAULT and/or ON UPDATE expression 
to match the declared column type.
```

To verify that the table was created with the proper data type, run the `SHOW CREATE TABLE` command (output is formatted for readability):

```sql
SHOW CREATE TABLE example\G

```

```output

       Table: example
Create Table: CREATE TABLE `example` (
  `col1` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `col2` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `col3` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col4` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  SORT KEY `__UNORDERED` ()
  , SHARD KEY () 
) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES,NO_AUTO_CREATE_USER' CHARACTER SET=`utf8mb4` COLLATE=`utf8mb4_bin`
```

The type of the `DEFAULT` and `ON UPDATE` clauses was updated to match the respective data type of `col1` and `col4` columns.

## Storage of CHAR(\<length>) as VARCHAR(\<length>)

For a column defined as type `CHAR` of length `len`, store the column as a `VARCHAR` of length `len` if `len` greater than or equal to the value of the engine variable `varchar_column_string_optimization_length`. If the value of the variable is `0`, the column is not stored as a `VARCHAR`.

Storing a `CHAR` of length `len` as a `VARCHAR` of length `len` will allow the column to realize the performance benefit of a `VARCHAR`, in many cases. For example, CHARs use 3 bytes of memory, and VARCHARs use 1 byte. VARCHAR can have better performance and use less memory during query execution because SingleStore does not need to allocate and process full-length strings with 3 bytes per char, as it does when using CHAR.

Suppose the value of `varchar_column_string_optimization_length` is `3` and you run:

```sql
CREATE TABLE ct_char(a CHAR(4));
```

The column `a` is stored as a `VARCHAR(4)`.

## CREATE TEMPORARY

`CREATE TEMPORARY` or `CREATE ROWSTORE TEMPORARY` (if `default_table_type` is set to columnstore, you must use the latter syntax) creates a table that will be deleted when the client session terminates.

```sql
CREATE TEMPORARY TABLE IF NOT EXISTS ct_temp_1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));
CREATE ROWSTORE TEMPORARY TABLE IF NOT EXISTS ct_temp_1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));
```

## CREATE ROWSTORE GLOBAL TEMPORARY

`CREATE ROWSTORE GLOBAL TEMPORARY` creates a table that exists beyond the duration of a client session. If a failover occurs, a global temporary table loses data and enters an errored state; the global temporary table needs to be dropped and recreated.

```sql
CREATE ROWSTORE GLOBAL TEMPORARY TABLE IF NOT EXISTS ct_temp_2 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));
```

## CREATE TABLE with an AUTO\_INCREMENT Column

Refer to [CREATE TABLE with an AUTO\_INCREMENT Column](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table-with-an-auto-increment-column.md) for more information.

## CREATE TABLE AS SELECT

`CREATE TABLE AS SELECT` (also referred to as `CREATE TABLE ... SELECT`) can create one table from results of a `SELECT` query.

Here is the basic syntax. You can create the new table and set shard keys, sort keys, and or other indexes:

```sql
CREATE [ROWSTORE] [REFERENCE | TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] <table_name_2> 
       (column_name(s), [SHARD KEY(column_name)] | [SORT KEY(column_name)] | [KEY(column_name)] 
       AS SELECT [*] | [column_name(s)] FROM table_name_1;
```

Here is an example of a `CREATE TABLE AS SELECT` command with a shard key, sort key and an index:

```sql
CREATE TABLE ctas_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b), KEY(a)) AS SELECT * FROM orig_table;
```

The table will include a column for each column of the `SELECT` query. You can define indexes, additional columns, and other parts of the table definition in the create\_definition. [Persisted computed columns](https://docs.singlestore.com/db/v9.1/create-a-database/using-persistent-computed-columns.md) can also be specified this way. Some examples:

```sql
CREATE TABLE table_1 (PRIMARY KEY (a, b)) AS SELECT * FROM table_2;
CREATE TABLE table_1 (SORT KEY (a, b)) AS SELECT * FROM table_2;
CREATE TABLE table_1 (a int, b int) AS SELECT c, d FROM table_2;
CREATE TABLE table_1 (b AS a+1 PERSISTED int) AS SELECT a FROM table_2;

```

In the case that the original table (`table_2` in the above examples) has an `AUTO_INCREMENT` column, it will be created as a non-auto-increment column in the new table (`table_1`).

## CREATE TABLE AS SELECT to Extract Data from One Column in an Existing Table

Extract time column from an event table to build a times table.

```sql
CREATE TABLE events (type VARCHAR(256), time TIMESTAMP);
INSERT INTO events VALUES('WRITE', NOW());
```

```sql
CREATE TABLE times (id INT AUTO_INCREMENT KEY, time TIMESTAMP) AS SELECT time FROM events;
```

```sql
SELECT * FROM times;

```

```output

+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2023-06-21 15:57:35 |
+----+---------------------+

```

## CREATE TABLE AS SELECT to Extract Distinct Values from an Existing Table

```sql
SELECT * FROM courses ORDER BY course_code, section_number;

```

```output

+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101      |              1 |              20 |
| CS-101      |              2 |              16 |
| CS-101      |              3 |              22 |
| CS-101      |              4 |              25 |
| CS-101      |              5 |              22 |
| CS-150      |              1 |              10 |
| CS-150      |              2 |              16 |
| CS-150      |              3 |              11 |
| CS-150      |              4 |              17 |
| CS-150      |              5 |               9 |
| CS-201      |              1 |              14 |
| CS-201      |              2 |              17 |
| CS-301      |              1 |               7 |
| CS-301      |              2 |              10 |
+-------------+----------------+-----------------+

```

```sql
CREATE TABLE IF NOT EXISTS distinct_courses (PRIMARY KEY(course_code))
    AS SELECT DISTINCT(course_code) FROM courses;
```

```sql
SELECT * FROM distinct_courses ORDER by course_code;

```

```output

+-------------+
| course_code |
+-------------+
| CS-101      |
| CS-150      |
| CS-201      |
| CS-301      |
+-------------+

```

## CREATE TABLE USING HASH

The `USING HASH` clause creates a [hash index](https://docs.singlestore.com/db/v9.1/create-a-database/other-schema-concepts.md) in a table.

If a rowstore or columnstore table is being created, the following applies:

* You can create single-column or multi-column hash indexes.
* When you create a unique single-column hash index, the shard key can contain only one column, and that column must be the same column that you have created the index on. When you create a unique multi-column hash index, the shard key must be a subset of the columns that you have created the index on.
* You can create multiple single-column hash indexes on a reference table.

If a columnstore table is being created, the following applies:

* You can create at most one unique hash index. You can create multiple multi-column hash indexes.
* You cannot create a unique hash index on a `FLOAT`, `REAL`, or `DOUBLE` column.

If a rowstore is being created, the following applies:

* Non-unique hash indexes on rowstore tables are not supported.
* When the `USING HASH` clause is used to define a non-unique index, a skiplist index is created.
* After a table has been created, the `SHOW WARNINGS` command will display a warning that a skiplist index was created instead of a hash index.
* The `SHOW INDEXES` command can be used to verify what types of indexes have been created.

## CREATE TABLE with Multiple Hash Indexes

The following example creates a columnstore table with three hash indexes. One of these indexes has a multi-column key.

```sql
CREATE TABLE articles_3 (
    id INT UNSIGNED,
    month int UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SHARD KEY(title),
    SORT KEY (id),
    KEY (id) USING HASH,     
    UNIQUE KEY (title) USING HASH,
    KEY (month,year) USING HASH);
```

The query `SELECT * FROM articles WHERE title = 'Interesting title here';` uses the hash index on `title` because the query contains an equality predicate on `title`. The query runs faster with the hash index than without.

The query `SELECT * FROM articles WHERE year > 2010 AND month > 5;` does not use the hash index on `month` and `year` since the query does not use an equality predicate.

See the [ColumnstoreFilter](https://docs.singlestore.com/db/v9.1/query-data/query-plan-operations.md) in the Query Plan Operations topic for an example `EXPLAIN` plan for a columnstore query that uses a hash index.

See [Highly Selective Joins](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore.md) for an example of a columnstore query with a join that uses a hash index.

## CREATE TABLE with One Hash Index Containing Multiple Columns

`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 ct_hash_1(a INT, b INT, c INT, KEY(a,b));
```

is equivalent to:

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

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.

Depending on the cardinality, the performance of the query may be worse than the performance of the same query, where `t` is a rowstore table and `KEY(a,b)` is defined on that table.

## CREATE TABLE with FULLTEXT Columns

SingleStore supports [full-text search](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-full-text-search.md) across text columns in a columnstore table using the `FULLTEXT` index type. A full-text index can only be added in a `CREATE TABLE` or `ALTER TABLE ADD FULLTEXT` statement and only on the text types `CHAR`, `VARCHAR`, `TEXT`, and `LONGTEXT`[Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/string-types.md)

If you query a column `c` that is part of a multi-column `FULLTEXT` index, where the query uses a `FULLTEXT MATCH` on `c`, the index on `c` will be applied.

This differs from a multi-column non-`FULLTEXT` index, where behavior is as follows: if you query column `c` that is part of index `i`, where the query uses an equality filter on `c`, the index on `c` will only be applied if `c` is the leftmost column in `i`.

Any column that is part of a `FULLTEXT` index can be queried, even if it is not the leftmost. Searches across `FULLTEXT` columns are done using the `SELECT ... MATCH AGAINST` syntax. For more information, see [MATCH](https://docs.singlestore.com/db/v9.1/reference/sql-reference/full-text-search-functions/match.md).

## CREATE TABLE with FULLTEXT Index on Two Columns

This example creates a `FULLTEXT` index for both the title column and the body column. Either column could be queried separately using `MATCH <column_name>`, and the index on the column would be applied. The `USING VERSION 1` syntax is optional.

```sql
CREATE TABLE articles_1 (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),
    FULLTEXT USING VERSION 1 (title,body));

```

## CREATE TABLE with Version 2 FULLTEXT Index

This example creates a `FULLTEXT` index for both the title column and the body column. Either column can be queried separately using `MATCH (TABLE <table_name>) AGAINST (<expression>)`, and the index on the column will be applied.

Refer to [Working with Full-Text Search](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-full-text-search.md) for more information about version 2 full-text search.

```sql
CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),    
    FULLTEXT USING VERSION 2 art_ft_index (title, body));
```

```sql
INSERT INTO articles (id, year, title, body) VALUES
    (1, 2021, 'Introduction to SQL', 'SQL is a standard language for accessing and manipulating databases.'),
    (2, 2022, 'Advanced SQL Techniques', 'Explore advanced techniques and functions in SQL for better data manipulation.'),
    (3, 2020, 'Database Optimization', 'Learn about various optimization techniques to improve database performance.'),
    (4, 2023, 'SQL in Web Development', 'Discover how SQL is used in web development to interact with databases.'),
    (5, 2019, 'Data Security in SQL', 'An overview of best practices for securing data in SQL databases.'),
    (6, 2021, 'SQL and Data Analysis', 'Using SQL for effective data analysis and reporting.'),
    (7, 2022, 'Introduction to Database Design', 'Fundamentals of designing a robust and scalable database.'),
    (8, 2020, 'SQL Performance Tuning', 'Tips and techniques for tuning SQL queries for better performance.'),
    (9, 2023, 'Using SQL with Python', 'Integrating SQL with Python for data science and automation tasks.'),
    (10, 2019, 'NoSQL vs SQL', 'A comparison of NoSQL and SQL databases and their use cases.');

OPTIMIZE TABLE articles FLUSH;
```

```sql
SELECT * FROM articles WHERE MATCH (TABLE articles) AGAINST ('body:database');

```

```output

+----+------+---------------------------------+-----------------------------------------------------------------------------+
| id | year | title                           | body                                                                        |
+----+------+---------------------------------+-----------------------------------------------------------------------------+
| 7  | 2022 | Introduction to Database Design |	Fundamentals of designing a robust and scalable database.                   |
| 3  | 2020 | Database Optimization	      | Learn about various optimization techniques to improve database performance.|
+----+------+---------------------------------+-----------------------------------------------------------------------------+

```

Refer to the [MATCH](https://docs.singlestore.com/db/v9.1/reference/sql-reference/full-text-search-functions/match.md) page for more legacy and version 2 full-text examples.

## Errors

These are the possible errors you may encounter when using `FULLTEXT`.

| Error                                                                  | Error String                                                     |
| ---------------------------------------------------------------------- | ---------------------------------------------------------------- |
| Invalid Type specified for column                                      | Invalid type specified for`FULLTEXT`                             |
| Specifying`FULLTEXT`keyword more than once in a`CREATE TABLE`statement | `FULLTEXT`may only be specified once in a`CREATE TABLE`statement |
| Specifying the same column multiple times                              | Column may only be specified once in a`FULLTEXT`definition       |
| Specifying a column that is not defined on the table                   | Column not defined                                               |
| Specifying`FULLTEXT`on a row store table                               | Only column store tables may have a`FULLTEXT`index               |

## CREATE TABLE with VECTOR Index

SingleStore supports [indexed vector search](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-indexing.md) across [VECTOR](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/vector-type.md) columns in columnstore tables using the `VECTOR` index type. Vector indexes use Approximate Nearest Neighbor (ANN) search which is appropriate for very large data sets and/or use cases with high concurrency requirements for a nearest-neighbor search. Vector indexes can be added in `CREATE TABLE` or `ALTER TABLE` statements and are supported for indexes on a single column of type `VECTOR(<N>[, F32])` where `<N>` is the number of dimensions.

A variety of index types and configuration parameters are available. SingleStore recommends using the IVF\_PQFS and HNSW\_FLAT index types. Refer to [Vector Indexing](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-indexing.md), [Tuning Vector Indexes and Queries](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/tuning-vector-indexes-and-queries.md),[Configuring Full-Text Indexes](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/configuring-full-text-indexes.md), and [Configuring Vector Indexes](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/configuring-vector-indexes.md) for details.

## Example

This example creates an IVF\_PQFS `VECTOR` index for column `v` in a table named `vect`.

```sql
CREATE TABLE vect (k int, v VECTOR(2) NOT NULL);

INSERT INTO vect VALUES …

ALTER TABLE vect ADD VECTOR INDEX (v) INDEX_OPTIONS
 '{"index_type":"IVF_PQFS", "nlist":1024, "nprobe":20}';

OPTIMIZE TABLE vect FLUSH;

```

## CREATE TABLE WITH DEEP COPY

The `WITH DEEP COPY` feature creates a new table that will have the same table definition as the original table. `CREATE TABLE WITH DEEP COPY`  makes a full copy of the data and indexes in the original table. Computed columns will be recomputed during the `WITH DEEP COPY` process. Any operation (`INSERT`, `UPDATE`, `DELETE`, `ALTER TABLE,` or `DROP TABLE`) on the original table will not affect the copied table and vice versa.

The `WITH DEEP COPY` feature eliminates the need to run a `CREATE TABLE LIKE` followed by an `INSERT SELECT` statement. In one statement, a table can be created that contains all of the columns, data, and other metadata (such as indexes) of the table from which it is copied.

There are some cases where `WITH DEEP COPY` is not supported. Error messages will be generated if these cases are attempted.

Creating a rowstore deep copy of an existing table:

```sql
CREATE TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));
CREATE ROWSTORE TABLE ctdc_table LIKE orig_table WITH DEEP COPY;

```

```output

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'CREATE Explicit ROWSTORE TABLE LIKE' is not supported by SingleStore.
```

Creating a reference deep copy of an existing table:

```sql
CREATE TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));
CREATE REFERENCE TABLE ctdc_table LIKE orig_table WITH DEEP COPY;

```

```output

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'CREATE REFERENCE TABLE LIKE' is not supported by SingleStore.
```

## CREATE TABLE WITH DEEP COPY Same Database

```sql
CREATE TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));
```

```sql
INSERT INTO orig_table (a,b) VALUES (9,3), (5,2), (10,4), (12,7);
```

```sql
SELECT * FROM orig_table;

```

```output

+----+----+
| a  | b  |
+----+----+
| 10 | 4  |
| 12 | 7  |
| 5  | 2  |
| 9  | 3  |
+----+----+   

```

```sql
CREATE TABLE ctdc_table LIKE orig_table WITH DEEP COPY;
```

```sql
SELECT * FROM ctdc_table;

```

```output

+----+----+
| a  | b  |
+----+----+
| 10 | 4  |
| 12 | 7  |
| 5  | 2  |
| 9  | 3  |
+----+----+

```

## CREATE TABLE WITH DEEP COPY Across Databases

```sql
CREATE TABLE test1.orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));
```

```sql
INSERT INTO test1.orig_table (a,b) VALUES (9,3), (5,2), (10,4), (12,7);
```

```sql
SELECT * FROM test1.orig_table;

```

```output

+----+----+
| a  | b  |
+----+----+
| 10 | 4  |
| 12 | 7  |
| 5  | 2  |
| 9  | 3  |
+----+----+
```

```sql
CREATE TABLE test2.ctdc_table LIKE test1.orig_table WITH DEEP COPY;
```

```sql
SELECT * FROM test2.ctdc_table;

```

```output

+----+----+
| a  | b  |
+----+----+
| 10 | 4  |
| 12 | 7  |
| 5  | 2  |
| 9  | 3  |
+----+----+

```

## CREATE TABLE WITH SHALLOW COPY

The `WITH SHALLOW COPY` feature creates a new table that will have the same structure as the original table. `CREATE TABLE WITH SHALLOW COPY` is a metadata-only operation and will not duplicate the actual data. Any operation (`INSERT`, `UPDATE`, `DELETE`, `ALTER TABLE`, or `DROP TABLE`) on the original table will not affect the shallow copy table and vice versa.

The `WITH SHALLOW COPY` feature is useful when you want to test new data models or perform operations on existing tables without risking live data. After testing or modifying the copied table, it can be easily deleted or promoted to the main table.

Creating a shallow table copy is orders of magnitude faster and uses less disk space than creating a table (`CREATE TABLE new_table LIKE original_table`) and then inserting all the rows from the original table into the new table (`INSERT INTO new_table...SELECT * FROM original_table`). For example, it's possible to shallow copy a large table in a second whereas a full copy would take minutes.

When a shallow copy of a table is created, SingleStore copies some of the in-memory metadata of the table from which the copy is created ("source table"), i.e the columnar blobs and index blobs. In addition, if an in-memory rowstore segment exists in the source table, it is copied as a columnstore segment of the new table.

To promote a shallow copy table to a main table, drop the original table and then rename the shallow copy table to the original table name. This approach is faster for resuming query operations on the table. For optimal performance, ensure that autostats is enabled and that the background merger is turned on by executing `ALTER TABLE col_table BACKGROUND_MERGER=ON;` before using the table.

When creating a shallow copy of an existing table:

* The plancache of the source table is not reused.
* Autostats on the new table are disabled. As a result, queries on the new table may run with a worse execution plan. See [Disabling and Enabling Automatic Statistics](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-tasks/#section-idm4609582192816032788462996243.md) for how to enable autostats for the new table.
* The [background merger](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore/managing-columnstore-segments.md) is disabled for the new table. This can be changed by running an `ALTER TABLE` statement on the new table to enable it after the table is created:
  ```sql
  CREATE TABLE ctsc_table LIKE orig_table WITH SHALLOW COPY;
  ```
  ```sql
  ALTER TABLE ctsc_table BACKGROUND_MERGER=ON;
  ```
* An exclusive lock is taken on the original table which blocks operations on the table while a shallow copy is created.
* To verify the memory usage of a shallow copy table, use the [TABLE\_STATISTICS and INTERNAL\_TABLE\_STATISTICS](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/table-statistics-and-internal-table-statistics.md) views, just as with any other table.
* After a shallow copy is created, it is treated like any other columnstore table. This means that information about shallow copies is not specifically tracked, and currently it is not possible to retrieve a list of all shallow copy tables from information\_schema.

## CREATE TABLE WITH SHALLOW COPY Performance Example

This example creates a table `ctsc_r` with 4,194,304 rows, then does a shallow copy of it, and a full copy of it using a `INSERT INTO...SELECT` statement. The shallow copy is over 200 times faster on a small test system.

First, create a table of dummy random `id` values .

```sql
CREATE TABLE ctsc_r(id BINARY(16));
```

```sql
INSERT ctsc_r VALUES (sys_guid());
```

```sql
DELIMITER //

DO DECLARE 
    c BIGINT;
BEGIN
    SELECT COUNT(*) INTO c FROM ctsc_r;
    WHILE (c < 4*1024*1024) LOOP
        INSERT INTO ctsc_r SELECT sys_guid() FROM ctsc_r;
        SELECT COUNT(*) INTO c FROM ctsc_r;
    END LOOP;
END //

DELIMITER ;

```

```sql
SELECT COUNT(*) FROM ctsc_r;

```

```output

+----------+
| COUNT(*) |
+----------+
| 4194304  |
+----------+
```

Make a shallow copy of all rows of `ctsc_r` to `ctsc_r2`:

```sql
OPTIMIZE TABLE ctsc_r FLUSH;
CREATE TABLE ctsc_r2 LIKE ctsc_r WITH SHALLOW COPY;

/* shallow copy time = 0.02 sec */

```

```sql
SELECT COUNT(*) FROM ctsc_r2;

```

```output

+----------+
| COUNT(*) |
+----------+
| 4194304  |
+----------+

```

All rows were copied to `ctsc_r2` and took 0.02 seconds to execute.

Next, make a full copy of all rows of `ctsc_r` to `ctsc_r3`:

```sql
CREATE TABLE ctsc_r3 LIKE ctsc_r;
INSERT INTO ctsc_r3 SELECT id FROM ctsc_r;

/* full copy time = 4.86 sec */

```

```sql
SELECT COUNT(*) FROM ctsc_r3;

```

```output

+----------+
| COUNT(*) |
+----------+
| 4194304  |
+----------+

```

All rows were copied to `ctsc_r3` ; however, it took almost 5 seconds to execute.

## Restrictions

The `WITH SHALLOW COPY` option is not supported for the following operations: copying across databases, copying rowstore tables, copying temporary tables, or creating a temporary table as a shallow copy. Error messages will be generated if these cases are attempted.

Across databases:

```sql
CREATE TABLE test1.orig_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b));
CREATE TABLE test2.ctsc_table LIKE test1.orig_table WITH SHALLLOW COPY;

```

```output

ERROR 1706 (HY000): Feature 'shallow copy of a table across databases' is not supported by SingleStore.
```

Rowstore tables:

```sql
CREATE ROWSTORE TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a));
CREATE TABLE ctsc_table LIKE orig_table WITH SHALLOW COPY;

```

```output

ERROR 1706 (HY000): Feature 'shallow copy of non-columnstore tables' is not supported by SingleStore.

```

Temporary or rowstore global temporary tables:

```sql
CREATE ROWSTORE GLOBAL TEMPORARY TABLE orig_table (a BIGINT, b BIGINT, SHARD KEY(a));
CREATE TABLE ctsc_table LIKE orig_table WITH SHALLOW COPY;

```

```output

ERROR 1706 (HY000): Feature 'shallow copy of temporary and global temporary tables' is not supported by SingleStore.
```

Creating a temporary table as a shallow copy:

```sql
CREATE TABLE orig_table(a INT);
CREATE TEMPORARY TABLE ctsc_table LIKE orig_table WITH SHALLOW COPY;

```

```output

ERROR 1706 (HY000): Feature 'temporary or global temporary table that is created as a shallow copy' is not supported by SingleStore.
```

## CREATE TABLE with COMPRESSION = SPARSE

SingleStore supports sparse data compression for *rowstore* tables. Nullable structured columns can use sparse data compression. The data types of these columns include numbers, dates, datetimes, timestamps, times, and varchars.

Columns that use sparse data compression only store non-`NULL` data values. [Example 4](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table.md) discusses an excellent sparse data compression use case, which also includes the query to retrieve actual memory usage of rowstore tables that use sparse data compression.

Sparse compression has the following limitations:

* The `SPARSE` clause cannot be used for key columns. However, if a rowstore table uses sparse data compression using the `COMPRESSION = SPARSE` clause, then the key columns are stored in-row.
* The `SPARSE` clause cannot be used for columns where the non-`NULL` size of the column is greater than 15 bytes.

Refer to the [Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) topic for details.

## CREATE TABLE with COMPRESSION = SPARSE All Sparse Columns

The following example demonstrates the `COMPRESSION = SPARSE` clause. This clause indicates that all columns in the table will use sparse data compression.

```sql
CREATE ROWSTORE TABLE transaction_1(
    id BIGINT NOT NULL,
    explanation VARCHAR(70),
    shares DECIMAL(18, 2),
    share_price DECIMAL(18, 2),
    total_amount as shares * share_price PERSISTED DECIMAL(18,2),
    transaction_date DATE,
    dividend_exdate DATE,
    misc_expenses DECIMAL(18, 2),
    country_abbreviation CHAR(6),
    correction_date DATE,  
    settlement_date DATE
    ) COMPRESSION = SPARSE;

```

## CREATE TABLE with COMPRESSION = SPARSE Selected Sparse Columns

The following example demonstrates the `SPARSE` clause. This clause is applied to the columns that will use sparse data compression.

```sql
CREATE ROWSTORE TABLE transaction_2(
    id BIGINT NOT NULL,
    explanation VARCHAR(70) SPARSE,
    shares DECIMAL(18, 2) SPARSE,
    share_price DECIMAL(18, 2),
    total_amount as shares * share_price PERSISTED DECIMAL(18,2),
    transaction_date DATE,
    dividend_exdate DATE SPARSE,
    misc_expenses DECIMAL(18, 2) SPARSE,
    country_abbreviation CHAR(6),
    correction_date DATE SPARSE,  
    settlement_date DATE SPARSE
    );

```

## Listing Whether Columns use Sparse Compression

The following query lists the columns in the `transaction` table that was created in Example 2. The query indicates, for each column, whether the column uses sparse compression.

```sql
SELECT column_name, is_sparse FROM information_schema.columns
WHERE table_name = 'transaction_2';

```

```output

+----------------------+-----------+
| column_name          | is_sparse |
+----------------------+-----------+
| id                   | NO        |
| explanation          | YES       |
| shares               | YES       |
| share_price          | NO        |
| total_amount         | NO        |
| transaction_date     | NO        |
| dividend_exdate      | YES       |
| misc_expenses        | YES       |
| country_abbreviation | NO        |
| correction_date      | YES       |
| settlement_date      | YES       |
+----------------------+-----------+

```

## CREATE TABLE with COMPRESSION = SPARSE Use Case

Sparse rowstore compression works best on a wide table with more than half `NULL` values. The distribution of the `NULL` values in the table does not contribute to the amount of memory used.

For example, consider this wide table `t` having three-hundred columns:

```sql
CREATE ROWSTORE TABLE ct_sparse (
  c1 double,
  c2 double,
  …
  c300 double) COMPRESSION = SPARSE;

```

In SingleStore 7.3, table `t` was loaded with 1.05 million rows, two-thirds of which are `NULL`. To retrieve the actual memory usage (in GB) of table `t`, run the following command:

```sql
SELECT table_name, SUM(memory_use) memory_usage FROM information_schema.table_statistics 
  WHERE table_name = 'ct_sparse' GROUP BY table_name;

```

```output

+-------------+--------------+
| table_name  | memory_usage |
+-------------+--------------+
| ct_sparse   |         1.23 |
+-------------+--------------+

```

The following table lists the memory usage of table `t`, with and without sparse compression:

| Compression Setting | Memory Use | Savings (Percent) |
| ------------------- | ---------- | ----------------- |
| NONE                | 2.62 GB    | NA                |
| SPARSE              | 1.23 GB    | 53%               |

For this wide table with two-thirds `NULL` values, you can store more than twice the data in the same amount of RAM.

***

Modified at: June 11, 2026

Source: [/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table/)

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