3

I have a table with an IDENTITY column

[Id] int IDENTITY(1, 1) NOT NULL

After some rows beeing added/removed I end with gaps in Id values:

Id   Name
---------
1    Tom
2    Bill
4    Kate

Is there an easy way to compress the values to 

Id   Name
---------
1    Tom
2    Bill
3    Kate

?

Max
  • 19,654
  • 13
  • 84
  • 122
  • 8
    One question - Why do you want to do such a thing? – Oded Jan 04 '11 at 18:49
  • possible duplicate of [SQL Server and Identity column](http://stackoverflow.com/questions/2537204/sql-server-and-identity-column) – Oded Jan 04 '11 at 18:53
  • I need this to keep two different databases in sync. I cannot control the part which requires ID columns to be in sequence. The good thing is that this particular table is very rarely updated and has less than 100 records. – Max Jan 04 '11 at 19:05
  • If you need to keep two databases in sync you would do well to find a natural key to use. When to comes to copying the data between the servers you will not have any problems with duplicate primary keys. – Tony Jan 04 '11 at 19:54
  • so how did this work out for you? – KM. Apr 25 '11 at 20:12

4 Answers4

10

I would strongly recommend that you leave the identity values as they are.

if this ID column is used as a foreign key on another table, changing them will get complicated very quickly.

if there is some business logic where they must be sequential then add a new column ID_Display where you can update them using ROW_NUMBER() and keep them pretty for the end user. I never let end users see and/or dictate how I create/populate/store the data, and if they are bothering you about the IDs then show them some other data that looks like an ID but is not a FK or PK.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • 1
    I'm up-voting this answer, but I'll go a step further; I really can't think of a valid business logic case in which it would be advisable to be dependent upon or to expect a particular ordering of identity values. – Brian Lacy May 02 '14 at 16:02
4

I think it's pretty easy to create a 2nd table with the same schema, import all the data (except for the identity column of course; let the 2nd table start renumbering) from the first table, drop the first table and rename the 2nd to the original name.

Easiness may be in question if you'd have a ton of FK relationships to rebuild with other tables etc.

Tom
  • 1,269
  • 8
  • 12
  • I think this is the way to go. Thanks! – Max Jan 04 '11 at 22:46
  • i have the case with tons of FK to ohter tables. I use a smallint field for the UserId Field in the user table with autoidentity, i proyected that i will never have more than 32.767 users (stills holds true) Becouse UserId table is in a tons of tables, i don't want to change the field type to int, becouse it will also increase the space used (4bytes / 2bytes). – Burnsys Jun 28 '17 at 18:41
1

Well as far as I know the only way you can is manually update the values by turning Identity insert on..but you should really avoid doning such a thing in first place..also if you truncate the table it will not have those gaps.

Vishal
  • 12,133
  • 17
  • 82
  • 128
1

I cannot control the part which requires ID columns to be in sequence.

This sounds like there is program logic which assumes there are no gaps--correct?

I need this to keep two different databases in sync.

It's still not clear what you mean. If the actual values in the IDENTITY column are not meaningful (not used as foreign keys by other tables), you can just do this:

DELETE FROM db1.table
SELECT col1, col2, col3 /* leave out the IDENTITY column */ 
INTO db1.table FROM db2.table
egrunin
  • 24,650
  • 8
  • 50
  • 93
  • I can only change one database, the second database is read-only for me. The application which controls the second database requires that IDs in my database are in sequence. – Max Jan 04 '11 at 22:45