Hi All!

I was thinking of creating a custom generated IDs for my table. I would like the ID to be something like "HR001" or "IT001", the two letter prefix would indicate the dept it belongs to. Initially I thought of having a table that will hold all the seed values for the IDs but I realize that this could have some concurrency problems if there will be a multiple number of users are creating a record at the same time. So now I have totally no idea on how to deal with the concurrency problem.

I will not be using this as a primary key because I already have the Identity field to be my primary key, though of course this field would definitely be unique. I will just be using this to display in the UI.

Any inputs would be greatly appreciated.

Thanks!

Everytime you insert, depending on the DEPT, you should retrieve the last ID for the DEPT and Add one to it and insert, thus maintaining uniqueness. I am not sure if you are trying to do this from your database server or from the frontend application.

Since you use only three digits, this will restrict only to 999 rows per dept. You might consider increasing the digits based on the load you can think of for each dept.

Hope this helps.
Cheers
_________________________
- Vijay G
Strive for Perfection

Hi Vijay!

Actually that's what I have right now, but the thing is there could be a concurrency issue in that setup, because it is a two process 1.) Update the seed table, 2.) Insert a new record on the main table using the generated ID. Anything could happen between the two process.

I will not be restricting it to three digits, it's just that the minimum is 3 digits,

Thanks.

Hi,

Is your code put under BEGIN TRAN... COMMIT TRAN Blocks? So that either it completes the entire two step transactions or rolls back in case of any issues. That should take care of concurrency.

_________________________
- Vijay G
Strive for Perfection

I have, but someone told me that it will still cause deadlocks. I guess I really have to test it myself.