# 8. SQL Reference

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Reference
Datatypes
User Defined Types
XML Column Type
Identifier Case & Quoting
Wide Character Identifiers
Qualified Names
Literals, Brace Escapes
CREATE TABLE Statement
DROP TABLE Statement
CREATE INDEX Statement
DROP INDEX Statement
ALTER TABLE Statement
CREATE VIEW Statement
CREATE XML SCHEMA Statement
DROP XML SCHEMA Statement
Sequence Objects
INSERT Statement
UPDATE Statement
SELECT Statement
COMMIT WORK, ROLLBACK WORK Statement
CHECKPOINT, SHUTDOWN Statement
Stored Procedures as Views & Derived Tables
GRANT, REVOKE Statement
SET Statement
Best Effort Union
Standard and User-Defined Aggregate Functions
Virtuoso SQL Optimization
SQL Inverse Functions
SQL Grammar
Bitmap Indices

## 8.10. CREATE INDEX Statement

    index_column_commalist
    	: column opt_asc_desc
    	| index_column_commalist ',' column opt_asc_desc
    	;

    index_option
    	: CLUSTERED
    	| UNIQUE
    	;

    index_option_list
    	: index_option
    	| index_option_list index_option
    	;

    opt_index_option_list
    	: /* empty */
    	| index_option_list
    	;

    create_index_def
    	: CREATE opt_index_option_list INDEX index
    		ON new_table_name '(' index_column_commalist ')'
    	;

This creates an index on a table. Index names must be unique across a qualifier. The ascending / descending column attributes are recognized for compatibility but do not have any effect. The index is defined and populated at the execution of the statement. Pre-existing stored procedures and prepared statements will make use of the new index when appropriate.

The UNIQUE attribute enforces uniqueness of the specified columns across the table and subtables where the index is visible.

The CLUSTERED attribute is not recommended. It will cause keys to be unprefixed by key id, thus causing the key entries to be intermixed with entries of other CLUSTERED indices with adjacent values of key parts.

See:

See the Free Text section on creating free text indices.

Previous DROP TABLE Statement | Chapter Contents | Next DROP INDEX Statement