SQL Pocket Guide, 3rd Edition

Indexes, Creating

The basic CREATE INDEX statement syntax is:

CREATE INDEX falls_name ON upfall
   (name, open_to_public);

In this syntax, falls_name is the name of the index. The table to be indexed is upfall. The index is on the combined values of name and open_to_public.

Oracle and PostgreSQL allow you to assign an index to a tablespace:

CREATE INDEX falls_name ON upfall
   (name, open_to_public)
   TABLESPACE users;

Oracle and PostgreSQL also allow you to index column expressions:

CREATE INDEX falls_name ON upfall
   (UPPER(name), open_to_public);

This particular index is useful for resolving queries in which the WHERE clause predicates involve the expression UPPER(name).

Indexes on expressions are subject to various restrictions. SQL Server requires that such expressions return “precise” results, thus ruling out expressions returning or involving floating-point data types. Such expressions must also be deterministic, meaning that a given input always returns the same output, no matter the server or environmental settings.