Sybase ASE Identity reset

Dieser Post wurde aus meiner alten WordPress-Installation importiert. Sollte es Darstellungsprobleme, falsche Links oder fehlende Bilder geben, bitte einfach hier einen Kommentar hinterlassen. Danke.

Sybase ASE Express Edition is an enterprise level database server - for free. It recovers (usually) flawless from any unclean shutdown (server reset/power outage), but the identity column counters usually skip big after recovery leaving huge gaps in the numbering.

Sybase does this skip for the right reasons - to keep data consistency, but having a 50.000.000 items gap isn't what you usually want. Here is a short tutorial on how to fix this (everything which has a $ sign in front of it is a variable, replace it by your value):

1. Connect to the database using isql or interactive SQL
2. Issue a use $databasename command to select your database
3. Use SELECT next_identity('$tablename') to find out the value of the next row being inserted
4. Use SELECT TOP 10 $identity_column FROM $tablename ORDER BY $identity_column DESC to see the numbers of the newest rows. Usually this translates to SELECT TOP 10 id FROM $tablename ORDER BY id DESC
5. If your next_identity value is 500 and the highest used id is 499, everything is fine, skip to step 3 and the next table, if the next_identity is something like 500233 and your highest id is like 229 or 235, you may reset the next_identity value
6. Use sp_chgattribute $tablename, 'identity_burn_max', 0, '$new-identity-value' to set the next_identity value for this table.
7. Go to step 3 for the next table if everything goes well.
8. If Sybase ASE issued an error like "The value 31295 for 'identity_burn_max' attribute must be greater than or equalto the current maximum identity value 3000008085.", think about your identity gap. If one of the following is true for you, there is no (easy) way to change the next_identity counter:
   1. You might ever (in 10 years or so) fill the gap between the new next_identity value you'ld like to set and the next used value. You'll run into really big trouble (and I mean BIG trouble) if this ever happens!
   2. Having the gap is a visual thing only, there are no technical reasons (like too small variables in software).
   3. Continues numbering is not important at all.
9. If you're sure you need to change the next_identity value, use dbcc set_identity_burn_max($databasename, $tablename, '$new-identity-value') to force setting of a new next_identity value.

Be careful replacing the variables by your values. If you're working on the table foo, $tablename will become just foo while '$tablename' will be 'foo'. This is important, because (for example) SELECT next_identity(foo) will drop an error while SELECT next_identity('foo') will do the job.