ColumnStore Create Table

A database consists of tables that store user data. You can create multiple columns with the create table statement. The data type follows the column name when adding columns.

Notes:

• ColumnStore tables should not be created in the mysql, information_schema or test databases.
• ColumnStore stores all object names in lower case.
• CREATE TABLE AS SELECT is not supported, and will instead create the table in the default storage engine (XtraDB).
• A ColumnStore autoincrement column can be added by including an autoincrement comment at either the table level or the column level. Only one autoincrement can be defined per table.
• Compression level (0 for no compression, 1 for compression) can be set at the system level. If a session default exists, this will override the system default. In turn, this can be overridden by the table level compression comment, and finally a compression comment at the column level.
• A table can be created in the front end only by using a ‘schema sync only’ comment. This could be useful when the table has been created on one user module, and needs to be synced to others.
• The column DEFAULT value can be a maximum of 64 characters.
• For maximum compatibility with external tools ColumnStore will accept the following table attributes:
  • MIN_ROWS
  • MAX_ROWS
  • AUTO_INCREMENT

All of these are ignored by ColumnStore.The following statement creates a table called orders with two columns: orderkey with datatype integer and customer with datatype varchar:

CREATE TABLE orders (orderkey INTEGER, customer VAR-CHAR(45)) ENGINE=ColumnStore