Identity order

Hi,

I have some tables in a database with a identity (autoincrement) column (PK).

After several operations (INSERT, UPDATE and DELETE), some holes appeared in the identity column, like this:

ContactId Contact

1 John

2 Mary

5 Sam

9 David

where ContactId is the identity column.

Can I order the ContactId column, by removing the empty spaces, in order to the table appears like this?:

ContactId Contact

1 John

2 Mary

3 Sam

4 David

(I'm using SQL Server 2005.)

Thank you in advance.

Identity values are meant to be unique (but not technically guaranteed) but they won't necessary be sequential or without any gaps. An identity is probably not going to work well for this scenario. You can create another table to manage this. And this may give you another option:

http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

However, you will still have issues with deletes - those won't be easy to manage if this is your requirement.

-Sue

|||

Sue:

Thank you for your answer.

I understand what you mean. But, after the table is filled with data, can I apply any command in order to put the identity values in sequential order? (Maybe 'ALTER INDEX' or 'DBCC' commands.)

I ask this, because I think identity column value must have a limit (maybe integers maximum limit in C language), and after long time with too much database operations, perhaps that limit be reached and several empty spaces (talking of auto generated values) remains in the table.

I beg your pardon for my silly question, but I'm newbie with SQL Server.

Anyway, thanks a lot.

--

Adrián

|||

You can change the number it's seeded at - for example if the next contact id number will be 100 but your last contact id in the table is 50 - using DBCC CHECKIDENT but it won't do anything about the gaps. For that, you would need to do something along the lines of creating a new table, populate the exiting data in the old table into the new table, drop the old table and rename the new table.

If the issue with gaps is that you want to use the number and are afraid you will run out of numbers, if the data type is an int, you can go up to 2,147,483,647 and then after exhausting positive values it will start using negatives through the value -2,147,483,648. So you have over 4 billion to work with there. If you double your storage space and use bigints the range is larger. It would handle thousands of ids generated per second over the course of over 100 years. I can remember the details but I really doubt that you would run out of numbers. You would of course want to use an appropriate data type due to the difference in storage required.

-Sue

|||

Thank you very much.

Your answer was very clear.