0

I have an existing sybase ase table which is using IDENTITY as its primary key. Now i need to recreate this table but i want to start the PK from next value of IDENTITY PK in prod env. e.g. If currently PK = 231 then after re-creating i want it to start from 232 onwards or any other INTEGER value > 231.

In oracle its easy to configure a sequence number and we can give start with but in sybase ase as we dont have sequence available so i tried using newid() function but it gives binary(16) values whereas i want integer values.

Can anyone suggest something ?

user3233451
  • 119
  • 2
  • 6
  • 17

2 Answers2

1

I am planning to use something like mentioned below and i think it will resolve my problem. Let me know if anyone has a better solution.

select abs(hextoint(newid()))

Any thoughts on this solution ? Can this ever generate the same number which it generated already?

user3233451
  • 119
  • 2
  • 6
  • 17
  • It may already be too late, but you can [check](http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/X11817.htm) this. – Meet Sep 02 '14 at 11:15
0

select next_identity('tablename') will return the identity value of the next insert for a table with an identity column so you know which ID will be allocated next.

Select @@identity immediately after an insert will return the ID which was just given to the row inserted.

However you need to be careful as identity columns are not the same as sequences and should not be relied upon if you want a sequence with no gaps because you will get a gap (albeit small sometimes) if the database crashes or is shutdown with nowait. For these a number fountain/insert trigger type generation of IDs is a better option. Using 'identity insert' is only really for when you want to bulk-load a whole table - you should not be setting that with every insert or you will defeat the whole purpose of an identity column, which is fast generation of new key values.

Rich Campbell
  • 566
  • 2
  • 9