CREATE INDEX Statement

Creates an index on columns in a specified table.

Category: Data Definition
Supports: EXECUTE Statement
Data source: SAS data set, SPD Engine data set, Aster, DB2 under UNIX and PC, Greenplum, MDS, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Syntax
Arguments
Details
Overview of Table Indexes
UNIQUE Keyword
See Also

Syntax

CREATE [UNIQUE] INDEX index ON table
(column [ASCENDING | DESCENDING ][, ...column]);

Arguments

UNIQUE

creates a unique index on a table.

index

specifies the name of the index.

Restriction For SAS data sets – If you are creating an index on one column only, then index must be the same as column. If you are creating an index on more than one column, then index cannot be the same as any column in the table.

table

specifies the name of the table that contains the column or columns to be indexed.

column

specifies the name of the column to which the index applies. Specify two or more column names to create a composite index.

Tip If you search two or more columns as a unit or if you have queries that involve only specific columns, use composite indexes.

ASCENDING

Rows are sorted from the smallest value to the largest value. This is the default value.

Alias ASC

DESCENDING

Observations are sorted from the largest value to the smallest value.

Alias DESC

Details

Overview of Table Indexes

An index stores both the values of a table's columns and a system of directions that enable access to rows in that table by index value. Defining an index on a column or set of columns enables SAS, under certain circumstances, to locate rows in a table more quickly and efficiently. Indexes enable SAS to execute the following classes of queries more efficiently:
  • comparisons against a column that is indexed
  • an IN subquery where the column in the inner subquery is indexed
  • correlated subqueries, where the column being compared with the correlated reference is indexed
  • join-queries, where the join-expression is an equals comparison and all the columns in the join-expression are indexed in one of the tables being joined
SAS maintains indexes for all changes to the table, whether the changes originate from FedSQL or from some other source. Therefore, if you alter a column's definition or update its values, then the same index continues to be defined for it. However, if an indexed column in a table is dropped, then the index on it is also dropped.
You can create simple or composite indexes. A simple index is created on one column in a table. A simple index must have the same name as that column. A composite index is one index name that is defined for two or more columns. The columns can be specified in any order, and they can have different data types. A composite index name cannot match the name of any column in the table. If you drop a composite index, then the index is dropped for all the columns that are named in that composite index.
Only the table owner can create an index on a table.
When you create an index, you must know the syntax that the data source supports. Index validation is performed by the data source. FedSQL provides the DBCREATE_INDEX_OPTS= table option to enable you to specify data source-specific index parameters for ODBC databases.

UNIQUE Keyword

The UNIQUE keyword causes SAS to reject any change to a table that would cause more than one row to have the same index value. Unique indexes guarantee that data in one column, or in a composite group of columns, remain unique for every row in a table. A unique index can be defined for a column that includes null or SAS missing values if each row has a unique index value.

See Also

Statements:
DROP INDEX Statement
Table Options:
DBCREATE_INDEX_OPTS=
Copyright © SAS Institute Inc. All rights reserved.