Chapter Contents


The SQL Procedure


Creates indexes on columns in tables.

Restriction: You cannot use CREATE INDEX on a table accessed via an engine that does not support UPDATE processing.

ON table-name (column <, column>...);


specifies a column in table-name.

names the index that you are creating. If you are creating an index on one column only, index-name must be the same as column. If you are creating an index on more than one column, index-name cannot be the same as any column in the table.

specifies a PROC SQL table.

Indexes in PROC SQL
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 PROC SQL to execute the following classes of queries more efficiently:

SAS maintains indexes for all changes to the table, whether the changes originate from PROC SQL or from some other source. Therefore, if you alter a column's definition or update its values, the same index continues to be defined for it. However, if an indexed column in a table is dropped, 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, the index is dropped for all the columns named in that composite index.

UNIQUE Keyword
The UNIQUE keyword causes the SAS System 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. For this reason, a unique index cannot be defined for a column that includes NULL or missing values.

Managing Indexes
You can use the CONTENTS statement in the DATASETS procedure to display a table's index names and the columns for which they are defined. You can also use the DICTIONARY tables INDEXES, TABLES, and COLUMNS to list information about indexes. See DICTIONARY tables .

See the section on SAS files in SAS Language Reference: Dictionary for a further description of when to use indexes and how they affect SAS statements that handle BY-group processing.

Chapter Contents



Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.