CREATE TABLE

Syntax

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>,...)
  | 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] [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 [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;

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;

Remarks

• For more information about the data types listed above, and for an explanation of UNSIGNED, refer to the Data Types 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 topic for more information.

• CREATE TABLE is slower in SingleStore than in MySQL. See Code Generation for more information.

• 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 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 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

  Copy

  CREATE TABLE ct_sort (col1 int, SORT KEY(col1 DESC));

  SQL

  Copy

  EXPLAIN SELECT * FROM ct_sort ORDER BY col1 DESC;

  +-------------------------------------------------------------------------------------------+
  | 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

  Copy

  EXPLAIN SELECT * FROM ct_sort ORDER BY col1;

  +------------------------------------------------------------------------------------+
  | 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.

• RESOLUTION is specific to index on geospatial columns. See Working with Geospatial Features for more information.

• COLUMNSTORE_SEGMENT_ROWS, COLUMNSTORE_FLUSH_BYTES controls configuration variables specific to columnstore tables. See Advanced Columnstore Configuration Options) 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.

• 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 for more information.

• This command can be run on the master aggregator node, or a child aggregator node (see Node Requirements for SingleStore Commands ).

• This command causes implicit commits. Refer to COMMIT 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>.

• <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.

• 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 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 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 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.

  The find the size of a column group index, refer to the How the Columnstore Works 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

  Copy

  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 Permission Matrix for the required permission.

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, see Data Types.

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:

CREATE TABLE ct_char(a CHAR(4));

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

Example(s)

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 TABLE IF NOT EXISTS ct_temp_2 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));

CREATE TABLE ct_auto (c1 INT AUTO_INCREMENT PRIMARY KEY);
INSERT INTO ct_auto (c1) VALUES (10);

SELECT * FROM ct_tb ORDER BY b;

+-------------------+------+------------+
| a                 | b    | c          |
+-------------------+------+------------+
|                 1 |    1 | from MA    |
|                 2 |    2 | from MA    |
|                 3 |    3 | from MA    |
|                 4 |    4 | from MA    |
|                 5 |    5 | from MA    |
| 13510798882111489 |    6 | from CA 96 |
| 13510798882111490 |    7 | from CA 96 |
| 13510798882111491 |    8 | from CA 96 |
| 13510798882111492 |    9 | from CA 96 |
| 13510798882111493 |   10 | from CA 96 |
| 14636698788954113 |   11 | from CA 20 |
| 14636698788954114 |   12 | from CA 20 |
| 14636698788954115 |   13 | from CA 20 |
| 14636698788954116 |   14 | from CA 20 |
| 14636698788954117 |   15 | from CA 20 |
|                 6 |   16 | from MA    |
| 15762598695796737 |   17 | from CA 17 |
| 13510798882111494 |   18 | from CA 96 |
|                 7 |   19 | from MA    |
| 14636698788954118 |   20 | from CA 20 |
+-------------------+------+------------+

CREATE REFERENCE TABLE ct_ref_1(id INT AUTO_INCREMENT PRIMARY KEY);
INSERT INTO ct_ref_1 VALUES();
INSERT INTO ct_ref_1 VALUES(5);
INSERT INTO ct_ref_1 VALUES();

SELECT id FROM ct_ref_1 ORDER BY id;

+----+
| id |
+----+
|  1 |
|  5 |
|  6 |
+----+

UPDATE ct_ref_1 SET id = 9 WHERE id = 5;
INSERT INTO ct_ref_1();

SELECT id FROM ct_ref_1 ORDER BY id;

+----+
| id |
+----+
|  1 |
|  6 |
|  9 |
| 10 |
+----+

DELETE FROM ct_ref_1;
INSERT INTO ct_ref_1 VALUES();

SELECT id FROM ct_ref_1 ORDER BY id;

+----+
| id |
+----+
| 11 |
+----+

CREATE REFERENCE TABLE ct_ref_2 (id int AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 7;
INSERT INTO ct_ref_2 VALUES (), ();

SELECT * FROM ct_ref_2;

+----+
| id |
+----+
|  7 |
|  8 |
+----+

CREATE TABLE IF NOT EXISTS my_MemSQL_table (id INT PRIMARY KEY AUTO_INCREMENT, v VARCHAR(10) NOT NULL);

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;

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

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;

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

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

SELECT * FROM times;

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

SELECT * FROM courses ORDER BY course_code, section_number;

+-------------+----------------+-----------------+
| 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 |
+-------------+----------------+-----------------+

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

SELECT * FROM distinct_courses ORDER by course_code;

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

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);

CREATE TABLE ct_hash_1(a INT, b INT, c INT, KEY(a,b));

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

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 articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),    
    FULLTEXT USING VERSION 2 art_ft_index (title, body));

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;

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

+----+------+---------------------------------+-----------------------------------------------------------------------------+
| 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.|
+----+------+---------------------------------+-----------------------------------------------------------------------------+

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;

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

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;

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

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

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

SELECT * FROM orig_table;

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

CREATE TABLE ctdc_table LIKE orig_table WITH DEEP COPY;

SELECT * FROM ctdc_table;

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

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

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

SELECT * FROM test1.orig_table;

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

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

SELECT * FROM test2.ctdc_table;

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

CREATE TABLE ctsc_r(id BINARY(16));

INSERT ctsc_r VALUES (sys_guid());

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 ;

SELECT COUNT(*) FROM ctsc_r;

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

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

/* shallow copy time = 0.02 sec */

SELECT COUNT(*) FROM ctsc_r2;

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

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

/* full copy time = 4.86 sec */

SELECT COUNT(*) FROM ctsc_r3;

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

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;

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

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

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

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

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

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 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
    );

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

+----------------------+-----------+
| 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 ROWSTORE TABLE ct_sparse (
  c1 double,
  c2 double,
  …
  c300 double) COMPRESSION = SPARSE;

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

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