Update table with sequential ints

Question

I have inherited a company database which issues an order number as a GUID. Not exactly user friendly when you want to quote your order number! So what I want to do is add a new int column and add a unique sequential order number to each existing order (which has been ordered by datetime).

It's a great idea, but I'm stuck on how to actually do it as an update query! Any help would be greatly appreciated.

Answer 1

Add an identity column to your table and the numbering will be taken care of for you.

alter table YourTable add YourTableID int identity

Answer 2

One way.

alter table t add order_id int identity

This will add an auto-incrementing int identity column. There is no guarantee as to the order in which the ids will be assigned to the existing rows, but a unique id will be assigned to each existing row. Each new row inserted after this change will get a new unique id.

Before applying this to a real application consider whether existing code will work with an identity column. Often this approach is a really harmless upgrade. Code that tries to insert an identity column fails, unless it uses set identity_insert. You can't remove the identity property without dropping the column.

To round this out might want a unique constraint on the new id, both for retrieval speed and to enforce uniqueness if the id column is ever updated.