Generating Unique Id in col

[Manoj Kumar]

Hi,

I have a column in database with identity column enabled so that every time a row is added i get a unique id to refer to that row. Everything is working very fine but there is one big issue. As the data expands the identity column goes on increasing in non-patterned way. there is only 10000 rows but around 40000 identity value is being inserted. I am not able figure out why this is happening. Is that something I need to set or something i'm missing. OR may there is another way to generate unique id for the row.

Regards,
Manoj

[Erland Sommarskog]

Post by Manoj Kumar
I have a column in database with identity column enabled so that every
time a row is added i get a unique id to refer to that row. Everything
is working very fine but there is one big issue. As the data expands the
identity column goes on increasing in non-patterned way. there is only
10000 rows but around 40000 identity value is being inserted. I am not
able figure out why this is happening. Is that something I need to set
or something i'm missing. OR may there is another way to generate unique
id for the row.

IDENTITY is intended for surrogate keys, which has no meaning in themselves.
Thus it is immaterial whether you get 1,2,3,4 or 1,2,8,9. Furthermore,
IDENTITY is designed for high-concurrent inserts. That is, if there are
two concurrent transactions inserting rows at the same time, you don't
want the second transacton to wait for the first to commit before it can
get a key value for the best throughput. To achieve this you must permit
for gaps. (The first transaction may roll back.)

Sometimes you need contiguous numbers, because of business requirements.
In this case you can do:

BEGIN TRANSACTION

SELECT @nextid = coalesce(MAX(idcol), 0) + 1 FROM tbl WITH (UPDLOCK)

INSERT tbl (idcol, ...) VALUES(@nextid, ....)

-- any other code here.

COMMIT TRANSACTION

This will give you less throughput, but there are many applications where
the throughput problem is not an issue. On the other hand, there are not
very many applications where contiguous numbers are required.

[Gert-Jan Strik]

A high number of gaps in the numbering is usually an indication of failed inserts.

For example, if the next number is 5, but the insert fails, then this number is gone, because the next number to issue would be 6.

If you want to find out if this is also the case in your situation, you should add proper error detection / handling, and you could log any insert failure.
--
Gert-Jan

Post by Manoj Kumar
Hi,
I have a column in database with identity column enabled so that every time a row is added i get a unique id to refer to that row. Everything is working very fine but there is one big issue. As the data expands the identity column goes on increasing in non-patterned way. there is only 10000 rows but around 40000 identity value is being inserted. I am not able figure out why this is happening. Is that something I need to set or something i'm missing. OR may there is another way to generate unique id for the row.
Regards,
Manoj

[Erland Sommarskog]

Post by Gert-Jan Strik
A high number of gaps in the numbering is usually an indication of failed inserts.
For example, if the next number is 5, but the insert fails, then this
number is gone, because the next number to issue would be 6.
If you want to find out if this is also the case in your situation, you
should add proper error detection / handling, and you could log any
insert failure.

Manoj is using SQL 2012, and they are caching IDENTITY in a way so
that you can lose a lot of them on a server restart. Big deal.