sybase identity数值重置

http://hi.baidu.com/blue_ranger/item/54b1f79a9a9dc6f02916479c

 

 

sybase identity数值重置

he depends on exactly what version you have, and on exactly what the situation is with your data.

If it's as simple as you suggest, where all the values that are currently in your table are OK, it's just the "next" value that's off, that's easier to repair.

Working backwards in versions...

If you are on ASE 12.5.1 or above (and I surely hope you are!), then it's very simple. All you need to do is run

sp_chgattribute [table_name], "identity_burn_max", 0, "[new_value]"

Note the third parameter must be the *integer* zero, and the fourth parameter is the new value to set to as a *string*. You will need an exclusive table lock on the table to do this, so if this table is currently in use, schedule a brief outage.

If you are pre-12.5.1, I'll tell you now, there is no method of fixing the next identity value that is supported by Sybase. You can either fool around with SELECT INTO a new table, which might change the identity numbers and need cleaning up for any relationships, or you can use an undocumented and unsupported dbcc command - which also needs an *immediate* shutdown with nowait (which in turn might give you identity gaps elsewhere, and is a pain).

If you're pre-12.5.1 and you want the undocumented unsupported shutdown with nowait method, Rob Verschoor has written a good stored procedure wrapper to it at www.sypron.nl/idfix.html.

Note that as of ASE 12.0 it has been possible to set an identity_gap value per table to limit the maximum possible gap of this sort. It's good practice to always set this for any table with an identity column.