Primary Key

ajayjain
ajayjain Member Posts: 119
edited 2013-05-17 in SQL Performance
Hi,

Just a quick question.

I need to create a table which will be heavily used every day by more than 100 users.

Table will have Document No. and Task No. as major fields
Should I create a table with 2 fields as primary key or 1 field

Table 1 (primary key on 2 columns)
Document No. Task No.(must be code data type)
DOC001 D1
DOC001 D2
DOC002 D1
DOC003 D1

Table 2
DOC001-D1
DOC001-D2
DOC002-D1
DOC003-D1

which one will you prefer. of-course I will have document no. in table 2 as well but not part of primary key.

Thanks
Ajay
Ajay Jain
UK

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    To give proper advice on database architecture I need much more information than what you just gave.

    However, combining fields does not make sense from a performance perspective.

    If you insert data without filtering for line numbers and read data using the get command the SQL row locking will do what it should. Locking on row level.
    Marije Brummel | Freelance Business Central Specialist
  • ajayjain
    ajayjain Member Posts: 119
    Hi Mark,

    We have lots of performance issues so I am little bit cautious to design tables.

    Document no. will stay in both case as a seperate field, i need to decide should I include into primary key or not
    Task no. will have to be code field and currently it is based on no. series and it is primary key
    so for every new record regardless of document no., it is TSK001, TSK002...TSK4999...
    This is most important key area in the whole database.
    So I am changing this so users will be on different pages and will not lock others
    DOC001-T01
    DOC001-T02
    DOC003-T01

    in both cases FINDLAST will be used with INCSTR for next no. with document no. filter.

    Thanks
    Ajay Jain
    UK
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    SQL Server does not lock pages, it locks eather rows or tables.

    Numberseries and findlast/incstr will lead to locking. Also with this type of PK you will need space in the key regarding fill factor.

    This is regardless of combining the key or not.

    My advice is to try to get budget to hire a performance specialist/database designer. It will cost some money now but save tons of money later.
    Marije Brummel | Freelance Business Central Specialist

Categories