Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    12

    Unanswered: Urgent Index Help need

    Hi ,

    I have primary key on tablea (fielda,fieldb)

    I created index idx1(fielda)

    Someone suggested that idx1 is useless
    Can anybody explain to me if it is true ?


    Regards
    Mahesh
    Regards
    Mahesh
    Senior DBA
    Calypso Technology

  2. #2
    Join Date
    Sep 2002
    Location
    Germany, near Aachen
    Posts
    120

    Re: Urgent Index Help need


    I have primary key on tablea (fielda,fieldb)

    I created index idx1(fielda)

    Someone suggested that idx1 is useless
    Can anybody explain to me if it is true ?


    When you define a primary key, ASE will create a clustered index on this column automatically.
    So it is not necessary to create another index on this column.

  3. #3
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Urgent Index Help need

    Originally posted by Bernd Dulfer

    When you define a primary key, ASE will create a clustered index on this column automatically.
    So it is not necessary to create another index on this column.
    There is a small distinction to be made here.

    sp_primarykey does nothing to inforce the primary key, it only documents it so Power Designer, ErWin and their ilk can obtain this information.

    Using Declarative Referential Integrity a primary key constraint can be created either as part of the original creation statement or later added using alter table.

    create table table_A ( a int, b varchar(25),
    constraint p_key primary key (a))
    go

    Or:
    create table table_A ( a int, b varchar(25))
    go
    alter table table_A add constraint p_key primary key (a)
    go

    Adding the primary key constraint creates a unqiue index automatically. If the key word "clustered" is specified after the keywords "primary key" the index will also be the table's clustered index. For this to work, there must be no clustered index on the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •