Sybase Indexes

Sybase Indexes provides quick access to the table data based on the specified columns. Indexes speeds of data retrieval process. SAP ASE decides
whether to use the indexes for each query submitted for that table.

Type of Indexes in Sybase ASE:

Composite Indexes: These are created on more than one column. These can created when tow or more columns are searched as a unit for logical searching.

Unique indexes If we dont want to permit any two rows have to same values. If we add a new row, ASE checks for row existing or not.

Clustered Indexes
Clustered Indexes forces ASE server to sort and re-sort the rows of a table. So, their physical order is same as their logical order.We can have only one Clustered Indexes per table. It is often created on the primary key

Non-Clustered Indexes
In Non-Clustered Indexes, physical order do not require to be same as their indexed order.

Index creation Syntax:

  • We can create indexes by using create index syntax or by using the unique or primary key integrity
    constraints of the create table command.
  • We cannot create indexes on columns with bit, text, or image datatypes.
  • The owner of a table can create or drop an index at any time

Complete form of Index creation 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] [with consumers = x

Simple form of Index creation syntax:

create index Index_name
on table_name (column_name)

Examples:
create index to au_id column on authors table of pubs database.

create index au_id_ind
on authors(au_id)

Sybase Indexes

Creating composite indexes: Creating indexes on more than one column is called composite inxdexes.

create index au_name_index
on authors(au_fname, au_lname)

Drop Indexes:
Syntax:

drop index table.index_name

Example:

index authors.au_id_ind
go

Create cluster index:

create clustered index titleidind
on titles(title_id)

Create non-clustered index:

create nonclustered index postalcodeind
on friends_etc(postalcode)

Powered by k2schools