## Syntax ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR createIndexStatement_start((START)) createIndexStatement_start --> createIndexStatement_0_0[CREATE]:::quoted createIndexStatement_0_0 --> createIndexStatement_0_1[UNIQUE]:::quoted createIndexStatement_0_0 --> createIndexStatement_0_2[INDEX]:::quoted createIndexStatement_0_1 --> createIndexStatement_0_2 createIndexStatement_0_2 --> createIndexStatement_0_3[IF NOT EXISTS]:::quoted createIndexStatement_0_2 --> createIndexStatement_0_4[identifier] createIndexStatement_0_3 --> createIndexStatement_0_4 createIndexStatement_0_4 --> createIndexStatement_0_5[ON]:::quoted createIndexStatement_0_5 --> createIndexStatement_0_6[tableSpec] createIndexStatement_0_6 --> createIndexStatement_0_7["("]:::quoted createIndexStatement_0_7 --> createIndexStatement_0_8[identifier] createIndexStatement_0_8 --> createIndexStatement_0_9[ASC]:::quoted createIndexStatement_0_8 --> createIndexStatement_0_10[DESC]:::quoted createIndexStatement_0_8 --> createIndexStatement_0_11[","]:::quoted createIndexStatement_0_9 --> createIndexStatement_0_11 createIndexStatement_0_10 --> createIndexStatement_0_11 createIndexStatement_0_11 --> createIndexStatement_0_8 createIndexStatement_0_8 --> createIndexStatement_0_12[")"]:::quoted createIndexStatement_0_9 --> createIndexStatement_0_12 createIndexStatement_0_10 --> createIndexStatement_0_12 createIndexStatement_0_12 --> createIndexStatement_end((END)) ``` ## Purpose The `create index` statement creates an index on one or more columns of an existing table. An index speeds up the search for rows with a value of its columns. With `unique`, no two rows of the table may have the same values in the columns of the index; a row with a null in one of these columns is not compared with other rows. The name of an index is unique per table. An error is raised when the table already has an index of that name. With `if not exists`, an existing index of that name is left as it is and the statement affects no row. A column can be followed by `asc` or `desc`. A platform with ordered indexes, such as Oracle, SQL Server, PostgreSQL and MySQL, sorts the column of the index in that order. The in-memory storage keeps an index without order, and ignores it; the results of a query do not depend on it. The statement is available for tables of the in-memory storage, Oracle, SQL Server, PostgreSQL and MySQL. On the in-memory storage, a unique index is checked on every insert and update, and the statement fails when the rows already in the table violate it. On a database, the platform creates and checks the index. Other platforms raise an error. Available from release 28.0 and the beta release 27.1 before it. The word `unique` remains available as a name of columns, tables and aliases. ## Examples The following example creates a table in the in-memory storage, an index on one of its columns and a unique index on two columns: ```sql create or replace table orders@inmemorystorage ( order_id int32 , customer_id int32 , order_date datetime ) create index orders_n1 on orders@inmemorystorage (customer_id) create unique index orders_u1 on orders@inmemorystorage (customer_id, order_date desc) ```