NexusDB Guide > SQL Reference > SQL Statements > Schema Statements

NexusDB V3 Manual

CREATE INDEX statement

NexusDB Manual V3 > SQL Reference > SQL Statements > Schema Statements

Define a primary or secondary index.

Syntax

<index definition> ::=

CREATE [ UNIQUE ] INDEX index-name ON [ schema-name. ] table-name

( <index element> [ { , <index element> }... ] )

<index element> ::= column-name <sort options>

<sort options> ::=

[ <collate clause> ]

[ IGNORE CASE ]

[ <sort direction> ]

[ <null ordering> ]

<sort direction> ::= { ASC | DESC }

<null ordering> ::= NULLS { FIRST | LAST }

Usage

The CREATE INDEX statement creates a compiled index based on the columns and sort options of the specified index elements. Indexes are used to optimize search and filter conditions, and it is generally recommended to define indexes on all columns that are used as keys in JOIN and WHERE clause criteria. The speed of grouping and ordering operations will also improve greatly if the columns in the GROUP BY and ORDER BY clauses are supported by matching indexes.

Notes

§	The index, schema and table names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The current database is implicit if a schema name is not specified.

§	If UNIQUE is specified, then an UNIQUE constraint is implicitly defined for the table.

§	The collate clause is used to specify a Windows OS collation for character string and national character string index columns. If the clause is omitted, then the explicit or implicit collation in the column definition is used for the sort order of the index key.

§	IGNORE CASE makes the sort order case-insensitive.

§	ASC is implicit if a sort direction is not specified. Specify DESC to sort the index key in descending order.

§	NULLS FIRST sorts null values before all non-null values in the set, and is implicit if the null ordering clause is not specified. Specify NULLS LAST to have null values sorted at the bottom of the set.

Examples

1)	The following example creates a compound unique index on the sections table:

CREATE UNIQUE INDEX ix_courseID_teacherID ON sections ( courseID, teacherID )

2)	The following example creates a case-insensitive index on the students table with an explicit collation for the studentName column:

CREATE INDEX ix_studentName ON students ( studentName locale 1033 IGNORE CASE )

Conformance

NexusDB extensions

CREATE INDEX statement

nexus_logo