Creating and Updating Tables and Views

Creating an Index

An index is a file that is associated with a table. The index enables access to rows by index value. Indexes can provide quick access to small subsets of data, and they can enhance table joins. You can create indexes, but you cannot instruct PROC SQL to use an index. PROC SQL determines whether it is efficient to use the index.

Some columns might not be appropriate for an index. In general, create indexes for columns that have many unique values or are columns that you use regularly in joins.

Using PROC SQL to Create Indexes

You can create a simple index, which applies to one column only. The name of a simple index must be the same as the name of the column that it indexes. Specify the column name in parentheses after the table name. The following CREATE INDEX statement creates an index for the Area column in NEWCOUNTRIES:

proc sql;
   create index area
      on sql.newcountries(area);

You can also create a composite index, which applies to two or more columns. The following CREATE INDEX statement creates the index Places for the Name and Continent columns in NEWCOUNTRIES:

proc sql;
   create index places
      on sql.newcountries(name, continent);

To ensure that each value of the indexed column (or each combination of values of the columns in a composite index) is unique, use the UNIQUE keyword:

proc sql;
   create unique index places
      on sql.newcountries(name, continent);

Using 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.

Tips for Creating Indexes

The name of the composite index cannot be the same as the name of one of the columns in the table.
If you use two columns to access data regularly, such as a first name column and a last name column from an employee database, then you should create a composite index for the columns.
Keep the number of indexes to a minimum to reduce disk space and update costs.
Use indexes for queries that retrieve a relatively small number of rows (less than 15%).
In general, indexing a small table does not result in a performance gain.
In general, indexing on a column with a small number (less than 6 or 7) of distinct values does not result in a performance gain.
You can use the same column in a simple index and in a composite index. However, for tables that have a primary key integrity constraint, do not create more than one index that is based on the same column as the primary key.

Deleting Indexes

To delete an index from a table, use the DROP INDEX statement. The following DROP INDEX statement deletes the index Places from NEWCOUNTRIES:

proc sql;
   drop index places from sql.newcountries;

Top of Page