Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006

    Unanswered: table structure vs performance

    there are 2 table structures: which one is better for DML processing?
    create table table_a
    (seq_num numeric(10,0) identity, logdate smalldatetime default getdate(), row_id numeric(10,0),
    constraint i_tr primary key (seq_num) )
    lock datarows

    create table table_b
    (seq_num numeric(10,0) identity, logdate smalldatetime default getdate(), row_id numeric(10,0))
    lock datarows
    create unique nonclustered index i_tr on table_b(seq_num)

  2. #2
    Join Date
    Jan 2003
    Geneva, Switzerland
    There is no difference between the two. The declarative constraint used to define the PK will create the index (by default clustered), so you end up with mostly the same behavior.

    The only difference between the two is that the first will use a clustered index, and the second is non-clustered - but with a DOL table this doesn't make that much difference. Essentially with a clustered index ASE will attempt to place rows with PKs that are close on the same page, whereas without the clustered index new rows will be added at the end of the table (heap table). As the PK is an identity column I don't think you'll see any difference between the two.

    That being said you need to define "performance": do you have to optimize inserts into the table, or selects, or joins or...?


Posting Permissions

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