Sunday, February 19, 2012

Changing identity column value

Hi. Is there a way to remove a gap between identity column values? Some records were deleted and now we have identity column values that jump all over the place.

Normally, this is not a good idea. There are typically a number of problems that you add to your list of problems whenever you try to "solve this kind of problem." Yes, this can be done, however, some problems that you will have to address include:

Taking the table "offline"

Resolving referential integrity problems.

Dropping and recreating the table

verifying that the change is "correct"

|||Programatically you can do that using :


SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

(see Books Online)

if you have the order of identity:
1,2,5,6

with command above you can insert the records 3 and 4

No comments:

Post a Comment