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]
Usage
Component Integration Services processes the create index command 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 the create index command. If trace flag 11208 is turned on, Component Integration Services does not send the create index command 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 in order 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 the create index command 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.
For pre-release 10.0 SQL Server or Microsoft SQL Server 6.5, neither the max_rows_per_page or on segment_name clause is forwarded 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.
See Also
create index in the Adaptive Server Reference Manual.
