August 29, 2009 at 4:27 am
Hi guys, I have just starting working on sql server and sql.I have a problem to discuss.
i have a table employees which is having a column EmployeeId which is a primary key
I have to make it like EDBR1206 now every time a new row will be added the value of the numeric value is increased by 2 ,like the next one will be EDBR1208 .
Now how i have thought it is like i will fetch the last value of EmployyeId in Employee table and then increase the numerical part by 2.
Now my question is that it is going to be accessed by a website from front end.So at a time many users might be working on the same table.So how will i ensure that at a time only one user is having total control of last employeeid value,so that only he can increment it .
Like suppose EDBR1206 is the last employeeid value ,now this has to accessed by one person only at a time so that he can increment it and add a new employee data.If at a same time more than one user will have acess to EDBR1206 then it will create problem .
How will i solve this
August 29, 2009 at 7:20 am
Can you make the column an identity? That let's SQL handle the incrementing and you can be sure that it will work.
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
August 29, 2009 at 1:43 pm
I'd second Gail's suggestion of using an identity. It is easy to read and handles the concurrency.
You can use a GUID as well if you like, but those, IMHO, are harder to work with.
August 29, 2009 at 8:38 pm
No actually client wants like this only.Can we use locking for this
August 30, 2009 at 2:14 am
Tell your client that this is a really, really, really bad idea. Yes it's possible, but most implementations are either very slow as you need to ensure that there's only one insert at a time, or they produce incorrect results.
Why does the client not want an identity column?
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
August 30, 2009 at 9:48 am
sunil88_pal88 (8/29/2009)
No actually client wants like this only.Can we use locking for this
If your client insists and having into consideration an Employee table shouldn't have heavy inserting after initial load I would suggest to pack the "insert client" logic into a single proc or function, store sequence number in a configuration table and handle locking programatically.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 30, 2009 at 10:04 am
You can set the identity to increment by 2. Then you can have it compute a column with the EMP and the numerical value. That will give you what you desire and handle the locking.
A client shouldn't care how the technical part is implemented. If they do, you are not doing a good job of providing them the technical services.
August 30, 2009 at 10:30 am
PaulB (8/30/2009)
If your client insists and having into consideration an Employee table shouldn't have heavy inserting after initial load I would suggest to pack the "insert client" logic into a single proc or function, store sequence number in a configuration table and handle locking programatically.
Even that can get nasty. There has to be enough locking that two people can't read the same sequence number at the same time. Essentially it involves serialising inserts to the table. Not good for scalability.
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
August 31, 2009 at 3:56 am
I hope client can have some business need for keeping the employee id as e.g. EBRD02, EBRD04. In that only the numeric id can be generated by identity column in the table as suggested and you can concat the fixed text part in any view / in select query / SP based on application design. This will resolve your locking issue without any coding complexity and In the final output will also meet clients expectation.
September 1, 2009 at 6:37 am
sunil88_pal88 (8/29/2009)
No actually client wants like this only.Can we use locking for this
Are you implying that you must have the ID's in order without skipping a sequence?
2,4,6,;;;
or can you use
2,6,8,...
I ask this because as suggested above, IDENTITIY (2,2) would by far be the easiest method even if the ID is generated from another table.
If you can't skip numbers you may run into problems with using IDENTITY, as if a transaction fails the Identity seed won't rollback. You would need to do a reseed, or find the "skipped" number and do the INSERT with IDENTITY INSERT. If this is the case then you probably would want to generate your own numbers. This can be easilly and quickly accomplished using a EmpID table and sproc/function. You would just grab the next value from this table and increment it, so concurrent users shouldn't be a factor with locking.
As mentioned earlier, they shouldn't dictate how you derrive the numbers.
September 1, 2009 at 7:00 am
if i use stored proc and in that i take the last value and increment it then i do not have to use locking and my problem will also be solved
September 1, 2009 at 7:12 am
You may still need some extra locking, depending how you do the insert.
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
September 1, 2009 at 7:18 am
As Gail mentioned, you might need locking. In a multi-user system, 2 or more people could select the max value at the same time. That's the power of SQL, multiple people can read the same data simultaneously. No ms later, the same time.
September 1, 2009 at 3:35 pm
Why does the client not want an identity column?
Perhaps the client is a Professor?
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
September 2, 2009 at 1:21 am
The primary key field value has been autogenerated in the specified format whatever format we want. Lets you need "EBRD02", "EBRD04" etc. The last two characters incremented by 2.
One more problem of accessing one value at a time by one user has been also resolved.
You can contact on my email id sumitrawat18@gmail.com.
I have some questions regarding these
bye and take care
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply