Create Indexes

You must be the owner of a table to create or drop an index. The owner of a table can create or drop an index at any time, whether or not there is data in the table. Indexes can be created on tables in another database by qualifying the table name.

Before executing create index, turn on select into.

sp_dboption,'select into', true

The simplest form of create index is:

create index index_name
     on table_name (column_name) 

To create an index on the au_id column of the authors table, execute:

create index au_id_ind 
on authors(au_id) 

The index name must conform to the rules for identifiers. The column and table name specify the column you want indexed and the table that contains it.

You cannot create indexes on columns with bit, text, or image datatypes.

• Issue create index in Parallel
  SAP ASE includes a parallel form of create index that uses the query execution engine to more efficiently execute the command.
• Function-Based Indexes
  Function-based indexes contain one or more expressions as index keys.You can create indexes directly on functions and expressions.
• Create Indexes Without Blocking Access to Data
  Use the create index ... online parameter to create indexes without blocking access to the data you are indexing.
• Unique Indexes
  A unique index does not allow any two rows to have the same index value, including NULL. If data already exists, the system checks for duplicate values when the index is created and subsequently checks each time data is added or modified with an insert or update.
• IDENTITY Columns in Nonunique Indexes
  The identity in nonunique index database option automatically includes an IDENTITY column in a table’s index keys so that all indexes created on the table are unique.
• Ascending and Descending Index-Column Values
  You can use the asc (ascending) and desc (descending) keywords to assign a sort order to each column in an index. By default, sort order is ascending.
• Using fillfactor, max_rows_per_page, and reservepagegap
  fillfactor, max_rows_per_page, and reservepagegap are space-management properties that apply to tables, and indexes and affect the way physical pages are filled with data.