Primary Key

ajayjain

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

Marije_Brummel

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.

ajayjain

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

Marije_Brummel

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.