September 29, 2017 at 12:28 pm
Developer called. She need best practice to accommodate the following:
TableXYZ has a numeric KEY value, 1,2,3,4,5, etc...
Application needs to grab next available KEY then update the row to increment that key value for next insert
Problem is: 2 Users access table at same time and grab idnetical KEY value (eg. 6) and they're having issues...
What is best practice to obtain the next key value, update the row and set KEY to next value?
thx in advance
September 29, 2017 at 12:37 pm
Express12 - Friday, September 29, 2017 12:28 PMDeveloper called. She need best practice to accommodate the following:
TableXYZ has a numeric KEY value, 1,2,3,4,5, etc...
Application needs to grab next available KEY then update the row to increment that key value for next insert
Problem is: 2 Users access table at same time and grab idnetical KEY value (eg. 6) and they're having issues...
What is best practice to obtain the next key value, update the row and set KEY to next value?
thx in advance
An IDENTITY column should do it. Otherwise you could define a SEQUENCE and use that instead.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 29, 2017 at 2:17 pm
As Phil mentioned IDENTITY and SEQUENCE are the most common and easier to work with .
If not feasible something like this will be the next best thing e.g. you update and output the desired value in a single operation.
if object_id('tempdb..#t1') is not null
drop table #t1
create table #t1
(id int)
insert into #t1 select 1
declare @out table
(lastid int)
update t1
set id = id + 1
output deleted.id -- could be inserted.id if new ID is the desired value
into @Out
from #t1 t1 with (rowlock)
select * from @Out
Similar to a sequence you could also setup a table that contains numbers in sequence - 100k or 1million
then do something similar to (will need to get exact code one of my clients uses on 2008 R2 for this.
with topx as
(select top 1 *
from #t1 with (updlock, readpast)
order by id
)
delete topx
output deleted.id
into @Out
October 1, 2017 at 6:10 pm
Express12 - Friday, September 29, 2017 12:28 PMDeveloper called. She need best practice to accommodate the following:
TableXYZ has a numeric KEY value, 1,2,3,4,5, etc...
Application needs to grab next available KEY then update the row to increment that key value for next insert
Problem is: 2 Users access table at same time and grab idnetical KEY value (eg. 6) and they're having issues...
What is best practice to obtain the next key value, update the row and set KEY to next value?
thx in advance
As the others have stated, either IDENTITY or SEQUENCE is the best way to go.
I know this is going to sound a bit nasty and I apologize for that but this if really basic knowledge that's missing and I'm thinking only of the success of your project, whatever it is. I strongly recommend that you get someone on the project that knows something about databases and SQL other than them just being a place and method to store data. If you think that will cost too much, remember what "Red" Adair is famous for saying; "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply