Re: MAX + 1

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I want to say that this was an example in one of the locking model
explanations. I recall it being somewhere between snapshot isolation and
serializeable. Just thought I'd mention.

--
Aaron Nelson.


"Russell Fields" wrote:

Many connections can be running MAX(ID) at the same time, so there is the
possibility of a trying to insert a duplicate Primary Key. You will need to
cope with it.

If you are using SQL Server 2005 or higher, you can use TRY/CATCH to catch
the error on an insert and try again. This is useful if you believe that
you will get very few of these errors. If it looks like it would happen
regularly, then you should try another method. Here is a try/catch sample:

create table maxid(id int primary key)
insert into maxid values(1)

declare @id int
set @id = 1;
InsertCode:
begin try
insert into maxid values(@id)
end try
-- This code will loop forever until the row is inserted
-- You might prefer to give up after a while.
begin catch
select @id = max(id + 1) from maxid
goto InsertCode
end catch

select * from maxid
drop table maxid

At the following link is a pretty old discussion of this subject:
http://www.sybase.com/detail?id=860
However, the "Next Key Table" is still a safe method of controlling your own
keys if you need to do so.

And here is one guy's take on the subject:
http://www.iknowkungfoo.com/blog/index.cfm/2008/6/1/Please-stop-using-SELECT-MAX-id

RLF

"Rogers" <Rogers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:542C3275-F4A7-4EAA-B07E-3BF46F572D39@xxxxxxxxxxxxxxxx
Dear Professional,

I am creating new stored procedure but before inserting new row in the
table
I am using MAX(ID) + 1 to get the maximum nunmber and insert into table. I
don't want to use IDENTITY(1,1) becauase sometimes the IDs are not in
sequence.

I am wondering, if same stored procedure access simultaneously, do I get
primary violation error? I remember I encountered this problem three years
ago when I was working on e-commerce application.

Please advice

Thanks


.