Index

The Index category is located in the Root > Script > Objects category, and can contain the following items that define how indexes are modeled for your DBMS.

Item

Description

[Common items]

The following common object items may be defined for indexes:

  • Add

  • AfterCreate, AfterDrop, AfterModify

  • BeforeCreate, BeforeDrop, BeforeModify

  • Create, Drop

  • Enable, EnableOwner

  • Header, Footer

  • Maxlen

  • ModifiableAttributes

  • Options, DefOptions

  • ReversedQueries

  • ReversedStatements

  • SqlAttrQuery, SqlListQuery, SqlOptsQuery

For a description of each of these common items, see Common object items.

AddColIndex

Specifies a statement for adding a column in the Create Index statement. This parameter defines each column in the column list of the Create Index statement.

Example (ASE 15):

%COLUMN%[ %ASC%]

%COLUMN% is the code of the column defined in the column list of the table. %ASC% is ASC (ascending order) or DESC (descending order) depending on the Sort radio button state for the index column.

AlterIgnoreOrder

Specifies that changes in the order of the collection should not provoke a modify database order.

Cluster

Specifies the value to be assigned to the Cluster keyword. If this parameter is empty, the default value of the %CLUSTER% variable is CLUSTER.

CreateBefore Key

Controls the generation order of keys and indexes. The following settings are available:

  • Yes – Indexes are generated before keys.

  • No – Indexes are generated after keys.

DefIndexType

Specifies the default type of an index.

Example (DB2):

Type2

DefineIndex Column

Specifies the column of an index.

EnableAscDesc

Enables the Sort property in Index property sheets, which allows sorting in ascending or descending order. The following settings are available:

  • Yes – The Sort property is enabled for indexes, with Ascending selected by default. The variable %ASC% is calculated, and the ASC or DESC keyword is generated when creating or modifying the database

  • No – Index sorting is not supported.

Example (SQL Anywhere 10):

A primary key index is created on the TASK table, with the PRONUM column sorted in ascending order and the TSKNAME column sorted in descending order:

create index IX_TASK on TASK (PRONUM asc, TSKNAME desc);

EnableCluster

Enables the creation of cluster indexes. The following settings are available:

  • Yes - The Cluster check box is enabled in index property sheets.

  • No – Cluster indexes are not supported.

EnableFunction

Enables the creation of function-based indexes. The following settings are available:

  • Yes - You can define expressions for indexes.

  • No – Function-based indexes are not supported.

IndexComment

Specifies a Statement for adding a comment to an index.

Example (SQL Anywhere 10):

comment on index [%QUALIFIER%]%TABLE%.%INDEX% is
%.q:COMMENT%

IndexType

Specifies a list of available index types.

Example (IQ 12.6):

CMP
HG
HNG
LF
WD
DATE
TIME
DTTM

MandIndexType

Specifies whether the index type is mandatory for indexes. The following settings are available:

  • Yes – The index type is mandatory.

  • No - The index type is not mandatory.

MaxColIndex

Specifies the maximum number of columns that may be included in an index. This value is used during model checking.

SqlSysIndex Query

Specifies a SQL query used to list system indexes created by the database. These indexes are excluded during reverse engineering.

Example (AS IQ 12.6):

{OWNER, TABLE, INDEX, INDEXTYPE}
select u.user_name, t.table_name, i.index_name, i.index_type
from sysindex i, systable t, sysuserperms u
where t.table_id = i.table_id
and u.user_id = t.creator
and i.index_owner != 'USER'
[and u.user_name=%.q:OWNER%]
[and t.table_name=%.q:TABLE%]
union
select u.user_name, t.table_name, i.index_name, i.index_type
from sysindex i, systable t, sysuserperms u
where t.table_id = i.table_id
and u.user_id = t.creator
and i.index_type = 'SA'
[and u.user_name=%.q:OWNER%]
[and t.table_name=%.q:TABLE%]

UniqName

Specifies whether index names must be unique within the global scope of the database. The following settings are available:

  • Yes – Index names must be unique within the global scope of the database.

  • No – Index names must be unique per object
Parent topic: Script/Objects Category (DBMS)