Confused whether to generate a nextNumber or to use Autoincrement

In my office we generate an unique ID on a table by doing something like:

Declare @.ID int
SELECT @.ID= ISNULL(MAX(@.ID), 0) + 1 FROM MyTable

Are there any benefit in using this methods rather then the Autoincrement functionality provided by sql server?

Could somebody explain which method is better and why?
Hi,
I think there is a benefit is that you will not has gaps in the ID field if you rolled back your transaction.
if you have an identity field in a table and the next value for example will be 100 and you make an insert statement then rolled back the insert then the next insert statement will have 101. but your way you have the control
on the other side I think having the identity field will be more efficient in terms of performance because you dont have to aggregate to get the max|||On a heavily used system your method will likely get conflicts as several connections attempt to do the insert at the same time, retrieving the same max value, and then doing the insert. You'll have to have some incrementing retry. And you'll likely have reduced concurrency when compared to using IDENTITY.

Another alternative that removes the MAX aggregate is to have a table with a single row with the next value to use, but then you you have to increment that, locking out anyone else trying to do the same thing until your transaction completes .. and you'll likely end up rolling back your insert in some cases, thereby losing your incremented value and having gaps in your sequence.

IDENTITY was added to SQL Server as a solution to exactly these drawbacks to creating nextNumber values. You don't want to do a MAX aggregate every time, and you don't want to cause serialization through a next-value table.

So if you need unique values and you also need good concurrency, IDENTITY is a great option. Gaps are possible, but not necessarily a bad thing.

Don|||Thats exactly the kind of reply i was looking for.

What we do exactly we have a table called "SystemNumber"
where we put a ID and value and then we increment from there.

LIke " CustomerID 1212
next time somebody does an insert it will because CustomerID 1213 etc

If my understading is correct in a heavily used system this will method will lock out everyone else trying to do the same thing.
Doubt
If you want to retrieve the ID that "Autoincrement" generated you have to use @.@.Identity.

Does this cause performance issues?
Why is it that this method does not lock?

Do you have to enclose the @.@.Identity within a transaction in order not to retrieve somebody else identity?

What Im trying to say How do i retrieve my new identity and guarantee that it is my one and not somebody else?

Could you give an example or link that will clarify everything?
Thanks a lot|||

hi,
i want to generate a next number with out auto increment.I want to write a trigger so when ever the data into other columns the trigger should be fired and the row should be updated with incremented value
advance thanks

|||Re: "What Im trying to say How do i retrieve my new identity and guarantee that it is my one and not somebody else?"

I think the SCOPE_IDENTITY function is what you are after.

Regards|||Thanks for your reply.
I will read more about Scope_identity .I think this is what I am looking for.
|||I'll consider that you hav a table Mytable with the primary key ID and the field you want to increment named RowNum

declare @.ID int
declare c cursor local
for select ID from Inserted
open c
fetch next from c into @.ID
while @.@.fetch_status =0
begin
update @.tbl
set RowNum = (SELECT ISNULL(MAX(RowNum), 0) + 1 FROM MyTable )
where @.ID = ID
fetch next from c into @.ID
end
close c
deallocate c

|||> Gaps are possible, but not necessarily a bad thing.

And if gaps are a bad thing, could this be a nice candidate for a CLR function?
Maybe even a Microsoft sample?

CREATE FUNCTION GetCounterValue(@.counterName nvarchar(256)) RETURNS bigint

Say, a light-weight method that uses a named mutex and a counter (maybe in a memory-mapped file? or...) in tempdb, or somewhere smart.

Or you might have enough work to do already...

Gorm Braarvig