how to change seed for identity column?

[Former Member]

Suppose I have a table mytab(id, c1,c2,c3,...). There are 100 rows in the table.

c3 is integer.

I have c3 to identity column. Then ase fill data to c3 as 1,2,3, ...10.

Then I run the powerbuilder application to insert one row in this table, I expect 101 as the value for column c3 for this new row, but its actual value is something like 5000101, not 101.

How to resolve this problem?

[kevin_sherlock]

See the documentation about managing identity columns, and identity gaps at:

  http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1570/html/sqlug/X35353.htm

Also, I would take this opportunity to point out that identity columns are not the same as "sequences".  Although identity columns will always increase, they are only guaranteed to be unique.  The fact that you "expect" a particular value after an insert isn't consistent with the implementation here.

[former_member185199]

This large gaps happen because of ASE fetching big blocks of ID values at ones.

When then the Server is shut down with nowait the next fetched value is such large.

read this article to get common with identity gaps:

Identity gaps in Sybase ASE

[Former Member]

Switch on the identity_insert option for the mytab table.

set identity_insert mytab on

delete the row which have value of  5000101 and re-insert it with the proper value 101

I hope it will help you ..