Creating an Index for a SQL Anywhere Table

Create an index on a SQL Anywhere table to speed access to data rows by pointing SQL Anywhere to the location of table data on disk.

Prerequisites Before you can create an index, be sure the data server is running, and you created and established a connection to it.
Note: In SQL Anywhere databases, do not define an index on a column that is defined as a foreign key, because foreign keys are already optimized for quick reference.
  1. In the Databases folder in Enterprise Explorer, under the database connection profile, expand the tree for a database, expand the Schemas folder and then the tree for the appropriate schema owner.
  2. Right-click the Tables folder and select New > Index.
  3. Enter the General options for the index.
    Table 1. General options
    Field Description
    Index Name Enter a name for the index.
    Filter Column by Name Pattern Enter a filter expression for the columns to be displayed in the column list. For example, * lists all columns for the specified table.
    Select the Columns Select the columns to be used in the index.
    • Use Select All, Deselect All, and Invert Selection to change your current selections.
    • Use Up and Down to change the order of columns listed.
    • When you have selected the columns to be used in the index, click Add.
    Existing Indexes and Indexes to Be Created for Table Lists the index to be created and existing indexes for the specified table. To delete a particular index entry, highlight the entry and click Delete. To delete all index entries, click Delete All. This field appears for all tabs.
  4. Enter the Miscellaneous options for the index.

    Use Load Default to load defaults for this tab. Save settings to default with Save As Default.

    Table 2. Miscellaneous options
    Field Description
    Unique Make the index unique. A unique index permits no two rows to have the same index value, including NULL.
    Clustered Make the index clustered. In clustered indexes, table data is physically stored in the order of the keys on the index.
    Virtual Make the index virtual, which simulates the existence of an index without actually building a full index.
    Dbspace Specify the type of dbspace in which to place the index. The default is SYSTEM.
    Comments Enter comments for the index.
  5. Click Next to save or execute a generated script.
  6. Enter the Script options.
    Table 3. Script options
    Field Description
    Generate Script Generate DDL for the index or indexes.
    Enter or Select the Parent Folder Specify an existing project or create a new project in which to save the DDL.
    Single File Save all DDL to one file.
    Multiple Files Save the DDL for each index to its own file. SelectOptions to set the following multiple file options:
    • Prefix – a prefix for all DDL files.
    • Filename – use the index name or a fully qualified schema-table-index name as the file name for each DDL file.
    • Suffix – a suffix for all DDL files.
    • Extension – the file extension, for example, sql.
    Execute Immediately Execute the DDL immediately in addition to saving the DDL.
  7. Click Finish to create the index, which appears in the Indexes folder under the associated table.
Related tasks
Editing Table Schema Data
Creating an Index for an ASE Table
Creating an Index for a Sybase IQ Table
Editing an Index for a SQL Anywhere Table
Dropping an Index for a SQL Anywhere Table
Related reference
ASA Create Index Preferences

Send your feedback on this help topic to Sybase Technical Publications: pubs@sybase.com

Your comments will be sent to the technical publications staff at Sybase, Inc. For product-related issues or technical support, contact Sybase Technical Support at 1-800-8SYBASE.