how to keep Identity consistent?

Last Post 27 Apr 2006 09:38 AM by sqladmin. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
francomiao
New Member
New Member

--
21 Jun 2005 12:06 PM
how to keep identity ID consistency? For example, I have column here call "ID", identity seed set to '1', identity increament set to '1'. Let's say the column has data "1, 2, 3, 4, 5, 6, 7, 8, 9, 10", how to let '8' move forward to become '7', if '7' was deleted? I have encounterd this kind of problem, if one of those ID got deleted, the next ID won't move forward change to the one got deleted, please share your experience on that, thanks!


Franco
mwesch
New Member
New Member

--
21 Jun 2005 03:10 PM
You cannot do this with an IDENTITY column. You would have to write custom code to manage sequential id's. Better to design your database though so it doesn't depend on having no missing id's. You could incur a lot of overhead with ensuring any FK relationships are retained, and constantly having to rebuild indexes.
sqladmin
New Member
New Member

--
27 Apr 2006 06:37 AM
what about dbcc checkident () with reseed
thoughts?
sqladmin
New Member
New Member

--
27 Apr 2006 09:16 AM
well it's also useful if you don't have any fkeys on a table, and you need
to 'truncate' then you would want to use dbcc checkident () with reseed cause
naturally truncation will reset the identity.

sqladmin
New Member
New Member

--
27 Apr 2006 09:38 AM
hmm... yeah that would work if there weren't too many rows.

as we all know 'delete' is a logged operation, and when i say logged,
i mean if there were thousands of rows... then guess what....
each
row
deleted
will
be
logged

where as the 'truncate' will only log the 'deallocation' of page space
to that table.

any how... just thought i would mention.

cheers
You are not authorized to post a reply.

Acceptable Use Policy