IDENTITY Column Insert

Hi,
Version: SQL Server 2000 Standarad Edition.
----
Does SQL Server Insert value to the IDENTITY column in one action, or does
it INSERT other columns of the table and UPDATE the IDENTITY column as a
separate statement, internally?
If I issue an independent SELECT WITH NOLOCK on such a table, is there a
possibility of getting wrong value for the Identity column anytime?
Thanks,
payyansits atomic. no problems with duplicate.|||Hi
You will not get duplicates. When a row is inserted, the Identity value is
incremented. If that transaction rolls back, the identity value will not be
decremented and you will find 'holes' in the sequences. This is by design to
enable higher concurrency.
If you need an absolute guarantee of sequential numbers, you have to do it
through a trigger and thta implies holding locks that will hurt performance.
Issuing a SELECT with NOLCOK will result in uncommitted rows being returned.
In effect, you might be processing against rows that my not exist at a later
stage in your DB. Use with care.
Regards
Mike
"payyans" wrote:
> Hi,
> Version: SQL Server 2000 Standarad Edition.
> ----
> Does SQL Server Insert value to the IDENTITY column in one action, or does
> it INSERT other columns of the table and UPDATE the IDENTITY column as a
> separate statement, internally?
> If I issue an independent SELECT WITH NOLOCK on such a table, is there a
> possibility of getting wrong value for the Identity column anytime?
> Thanks,
> payyans