Creates an index on one or more columns in a table.
Transact-SQL Syntax
create [unique][clustered | nonclustered] index index_name
on [[database.]owner.]table_name(column_name [, column_name]...)
[with {{fillfactor | max_rows_per_page} = x, ignore_dup_key, sorted_data,
[ignore_dup_row | allow_dup_row]}]
[on segment_name]
ODBC Syntax
CREATE [UNIQUE] INDEX index_name
ON base_table_name
(column_identifier[ASC|DESC] [,column_identifier[ASC|DESC]]...)
prohibits duplicate index values (key values). The system checks for duplicate key values when an index is created and checks each time data is added with an insert or update. If a duplicate key value exists, or if more than one row contains a NULL value, the command aborts and an error message shows the duplicate value prints.
indicates that the physical order of rows on this table is the same as the indexed order of rows. Only one clustered index per table is permitted.
indicates that a level of indirection exists between the index structure and the data. Up to 249 nonclustered indexes per table are permitted.
specifies how full the DBMS makes each page when it creates a new index on existing data. This percentage is relevant only at the time the index is created. As the data changes, the pages are not maintained at any level of fullness. The default is 0. If the fillfactor is set to 100, the DBMS creates indexes with pages 100% full.
responds to a duplicate key entry into any table with a unique index. An attempted insert of a duplicate key is ignored, and the insert is canceled with an informational message.
specifies that the index is to be created on the named segment.
is the name of the index. Index names must be unique within a table but need not be unique within a database.
is the name of the table that contains the indexed column or columns.
is the column or columns to be included in the index. Composite indexes are based on the combined values of up to 16 columns. The sum of the maximum lengths of all the columns used in a composite index cannot exceed 256 bytes.
create index au_id_ind on authors (au_id)
create index ind1 on titleauthor (au_id, title_id)
create nonclustered index zip_ind on authors (zip) with fillfactor = 25
ASE/CIS sends the create index command to the DirectConnect server as a language event.
Columns of type bit, text, and image cannot be indexed.
You cannot create an index on a view.
These parts of the create index command are not recognized by transformation:
clustered | nonclustered
with fillfactor
max_rows_per_page
ignore_dup_key
sorted_data
ignore_dup_row | all_dup_row
on segment
The ODBC command ASC|DESC cannot be generated by transformation. Only ascending indexes can be created.
