Scope_Identity vs manually increment + 1 clarification needed

Hi
I was wondering if somebody could help clarifying the issue of

Scope_Identity vs manually increment + 1 .

Where I work since the days of sql server 7 we use our own way to get the next Identifier.
By having a table with (name and value column) EG CustomerID 234 and when we do an insert we update this Table by incrementing + 1 EG CustomerID 235.

I beleive that this way of doing things is not good and should be replaced by scope_Identity.

The reason I was given was to avoid Locking Tables whilst inserts executes whether in transactional operation or not.

I totally disagree with the above but cannot demostrate it.

My understanding of Scope_Identity is that is the most secure way to get the next Identifier by getting things
that are only in scope (EG A stored Procedure,statements etcc)

Now my question is:
Does scope_Indentity performs any locks of some sort ,or slows the process down?

Could somebody give me a example why incrementing by 1 manually is inefficient or what problem it can cause.

Thanks a lot in advance

No there is no lock for IDENTITY columns.

There are lot of points there to say manyally +1 is inefficeint.

1. You have to lock the record - identity never use any lock

2. You have to fetch the last value from your table - identity uses the LOG no query

3. You have to manullay increment the value - idenity does the same

4. You have to wirte the code manually on triggers - identity is internal opertion, you need not to write any code

5. Fetching the last inserted value , again a overhead to write a few line of code - idenity values can be fetched easily

Last but not least Idenity never cause any performance issue, but it may cause GAPs on your sequential value.

Want to know more see this article.. http://blogs.msdn.com/sqlprogrammability/archive/2006/04/04/567724.aspx

|||

Fantastic reply.Thanks!!!. If there are anymore points please add it here. I will wait a bit in case somebody else adds something else and then pack in a email and send it to my collegues who were not conviced.

Thanks again for such a clean answer