Creates an index on one or more columns in a table.
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]
Component Integration Services processes create index when the table involved has been created as a proxy table. The actual table resides on a remote server, and Component Integration Services forwards the request to the remote server after Adaptive Server catalogs are updated to represent the new index.
Trace flag 11208 changes the behavior of create index. If trace flag 11208 is turned on, Component Integration Services does not send create index to the remote server. Instead, Adaptive Server processes the command locally, as if the table on which it operates is local. This is useful for creating an index on a proxy table that maps to a remote view.
Adaptive Server performs all system catalog updates to identify the index. However, just as there are no data pages in the server for proxy tables, there are no index pages.
When Component Integration Services forwards create index to a remote server, the table name used is the remote table name, and the column names used are the remote column names. These names may not be the same as the local proxy table names.
Component Integration Services forwards everything except the on segment_name clause to the remote server.
Component Integration Services forwards everything except the on segment_name clause to the remote server.
Component Integration Services forwards everything except the on segment_name clause to the remote server.
When the language capability is set to “Transact-SQL”, Component Integration Services forwards all syntax except the max_rows_per_page and on segment_name clauses to the remote server.
When the language capability is set to “DB2”, the behavior is the same as for server class db2.
The DirectConnect must either translate the Sybase extensions to equivalent native syntax or ignore them.
Component Integration Services does not forward the following clauses to the remote server:
on segment_name
max_rows_per_page
ignore_dup_key
ignore_dup_row
allow_dup_row
Component Integration Services converts the fillfactor option to pctfree and then forwards it to the remote server.
create index in the Reference Manual
