Sybase ASE/REP Interview Questions

Creation of Indexes:

You can create the index either of two ways:

• With Create index command

• By specifying Integrity Constraints like Primary Key and Unique Key in create table command.

Integrity constraints (Primary and Unique Keys) have following restrictions for the indexes:

• You cannot create non unique indexes.

• You cannot set various setting provided by create index command like ignore_dup_key,ignore_dup_row etc.

• You cannot drop these indexes without alter table command.

When we specify the Primary Key in create table command, it creates Unique Cluster Index and unique Key creates unique non clustered index on the column mentioned in keys.

Summarize as -

• Primary Key in create table command ==> Creates Unique Clustered Index

• Unique Key in create table command  ==> Creates Unique non clustered Index

• If neither the clustered nor the nonclustered keyword is used ==> ASE will create non clustered indexes.

• If unique keyword is not used in create index command ==> ASE will create non unique indexes.

Create Index Command Syntax:

create [unique] [clustered | nonclustered] index index_name
on [[database.]owner.]table_name
(column_expression [asc | desc]
[, column_expression [asc | desc]]…)
[with {fillfactor = pct,
max_rows_per_page = num_rows,
reservepagegap = num_pages,
consumers = x, ignore_dup_key, sorted_data,
[ignore_dup_row | allow_dup_row],
statistics using num_steps values}]
[on segment_name]
[index_partition_clause]

Before executing create index, turn on select into: sp_dboption,’select into’, true

The simplest form of create index is: Create index index_name on table_name (column_name)

Viewing Indexes:

• Using sp_helpindex we can view indexes of a table. E.g. sp_helpindex ‘tablename’

• sp_statistics also  returns a list of indexes on a table. E.g. sp_statistics ‘tablename’

• In addition, if you follow the table name with “1”, sp_spaceused reports the amount of space used by a table and its indexes. E.g. sp_spaceused ‘tablename’,1

Dropping indexes

• The drop index command removes an index from the database.

• Only the owner of an index can drop it. drop index permission cannot be transferred to other users. The drop index command cannot be used on any of the system tables in the master database or in the user database.

•  You cannot drop indexes using drop index command which were created using Integrity constraint. To drop the same indexes you should use alter table command.

Index Option ( for Create Index Command):

 i)ignore_dup_key  :

• This option is only for unique clustered and non clustered indexes.

• If you try to insert a duplicate value into a column that has a unique index, the command is canceled. You can avoid this situation by including the ignore_dup_key option with a unique index. Your command would be successful and it will ignore that key value (It means, finally no insert on the table).

• You cannot create a unique index on a column that already includes duplicate values, whether or not ignore_dup_key is set.

ii) ignore_dup_row and allow_dup_row:

• These options are only for the non unique clustered index.

• These options are not relevant when creating a nonclustered index. Since an Adaptive Server nonclustered index attaches a unique row identification number internally, duplicate rows are never an issue—even for identical data values.

• A nonunique clustered index allows duplicate keys, but does not allow duplicate rows unless you specify allow_dup_row. If allow_dup_row is set, you can create a new nonunique, clustered index on a table that includes duplicate rows, and you can  insert or update duplicate rows.

• The ignore_dup_row option eliminates duplicates from a batch of data. When you enter a duplicate row, Adaptive Server ignores that row and cancels that particular insert or update with an informational error message.

• If a table has duplicate rows and you are creating non unique clustered index with ignore_dup_key, it will delete all the duplicate rows from the table.

iii)Sorted Data:

• The sorted_data option of create index speeds index creation when the data in the table is already in sorted order. sorted_data speeds indexing only for clustered indexes or unique nonclustered indexes.

• Creating a nonunique nonclustered index is, however, successful, unless there are rows with duplicate keys. If there are rows with duplicate keys, an error message appears and the command is aborted.

Source : http://sybooks.sybase.com                                                                                                                                  Continue…