RE: IDENTITY Column Insert

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

From: Mike Epprecht (SQL MVP) (mike_at_epprecht.net)
Date: 01/13/05 

Date: Wed, 12 Jan 2005 23:11:02 -0800

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 NOL*** 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

Quantcast