We have a table that was hastily created and now we realize that we need to add an identity column to the table. The table is very simple and has no index or primary key. It is just a logging table and the only thing we ever do is add rows to it. We do not do any deletes or updates.
We would like it so that, as much as possible, when we add the identity column, it will be numbered in the sequence in which the rows were added to the table. So let's say the identity column is named ID, we want the first row inserted to the table to have ID=1, the second row to have ID=2, the third row to have ID=3, and so on.
Is this possible at all? If it's not perfectly possible, we would find it acceptable if it works to some degree at least, i.e., some sets of rows are sequentially numbered in the order they were inserted, while some are not. So, will the following command do this?
alter table MyTable add ID int identity(1,1)
Thank you so much in advance!