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