Autoincrement Field for Multi-company database

Question

Autoincrement Field for Multi-company database

Viewing 3 posts - 16 through 17 (of 17 total)

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

d viz Ten Centuries Points: 1092 More actions · Answer 1

OK I perfectly understand this as I have had the same problem in the past. My solution was a new table that has the fields:

1. Company Code

2. String Identifier for the number range

3. The actual last number used

so every insert in any table that I need to have a serial number per company code, first updates this Number range table then uses the number value to insert.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

That's referred to as a "sequence table" and it one of the things that was suggested. But, you must be VERY careful how you do the update to it or you will create a world of deadlocks you never planned on. Also, it will require some pretty special handling if you want to insert more than one row at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

goodguy SSCertifiable Points: 5456 More actions · Answer 3

I cast my vote with all those who advised against sequence tables and promoted pure single-series auto-incrementing.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons