Best Way For Primary Key

Question

Post reply

Best Way For Primary Key

rohini-145252

Old Hand

Points: 328
More actions
November 3, 2006 at 12:01 am

#114911

Greetings.
I need to know the best method to get a unique number. i.e Primary Key
For Eg:
TableName : TrHead
CompID      FYr        JID        TrnNo
BM      2006-2007 CaP      00001
BM      2006-2007 CaP       00002
The TrnNo should be unique for CompID,FYr,JID.
Method1:
I have a table that 'Journal' that has
CompID     FYr   JID    LastNoH
BM      2006-2007 CaP 00002
EM      2006-2007 CaP 00015
I fetch the LastNOh from Journal and add 1 and Insert a new entry in TrHead and after inserting i update the LastNOH in journal table.
Method 2:
I get max(TrnNO) from TrHead and add 1 and insert into TrHead
Method 1 includes select and update statement whereas Method 2 includes only a select stmt
When i update the Journal table,some other user selects rows from Journal table and there comes deadlock.
So does that mean my update stmt locks the entire table, if yes then how shall i block that row alone. Is With ROWLOCK enough?
Any help Appreciated.
Regards
Rohini

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

1. Don't use cursors. DO everyting in a single set based statement

2. Set up indexes.

3. If you do corelated INSERT/UPDATE on more than 1 table use transactions

But actually the wole design is wrong.

You "Journal" must be a view aggregating values from "TrHead". Then all that activity you are trying to put in code will be done behind the scene.

_____________
Code for TallyGenerator

rohini-145252 Old Hand Points: 328 More actions · Answer 2

1.I am not using cursors.

2.All the fields i am using in where condition are primary keys (which i have already mentioned)

I just want to know the optimised method to find the last TrnNo. That is all.

My journal table is not a view @ all.It is a master table which holds the different Jrl Types.It has LastNOH for each JrlID for each CompID.

I just want to know if it is optimised to have the LastNoH there and select and update that to get the last No OR it is better to use Max() in Transaction table itself.

And also how to Lock a table for a row alone in an update stmt.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Make the column an identity. Let SQL worry about the next number.

If you do increments yourself, unless you're very careful with transactions, you could end up with duplicate values (or in your case, a duplicate key error) due to race conditions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110208 More actions · Answer 4

It's better to use

CREATE VIEW dbo.journal

SELECT CompID, FYr, JID, Max(TrnNo) as LastNoH

FROM TrHead

GROUP BY CompID, FYr, JID

That's it.

You may create index on this view to make it table. SQL Server will do everything for you. Your company have already paid for this purchasing license.

_____________
Code for TallyGenerator

rohini-145252 Old Hand Points: 328 More actions · Answer 5

I repeat Journal is a master table for Journal Types. and in that i am holding the lastno.thats it.

Also I do not want identity...i want a unique transaction no within CompID,FYr,JrlID.

I just want to know the better of 2.

1.To have a table- select from it and update OR

2.Use Max().

Please let me know

Vladan SSC-Insane Points: 22072 More actions · Answer 6

What Sergiy is trying to say is that it SHOULD NOT be a table, but a view.

You are asking us to tell you which of two bad methods is better... sorry, I don't know how to answer such question.

Using IDENTITY is the best option.