Sequence of Rows Numbered When Adding an Identity Column

Transact-SQL
1

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!

2

You cannot add identity property to an existing column.
You can add a new column with identity and then delete or rename the old column.

3

YES that command will do it

4

Do you have a datetime or some other column in the table that would provide the order of INSERTion? Then you could do it by creating a new table, with an identity, to replace the old table.

Edit: This would guarantee that the identity numbers were all in the proper sequence when assigned.

5

Why do you believe you need an identity column added to the table? What is the purpose of that column and how will it help resolve the issues you now have?

There may be better options - depends on how the table is utilized and what issues you are trying to resolve.

1 Like
6

Unfortunately, no. We don't have a column that tells the date/time of insertion.

7

Well, this table logs all the changes made to another table. Every time a change is made to that other table, the old record in that other table is logged here. But now our client wants to know which fields in that other table are the most often ones that are being changed. So we need to trace back in the log table the history of changes made to the fields in sequential fashion. But the lack of a column to indicate the date/time of insertion in the log table has put us in a quandary. We were hoping that by adding an identity field, the default numbering sequence that SQL Server implements for this new identity field will follow the sequence in which the records were inserted to the log table. Will it?

This brings me to the question, if there is no index or primary key at all, how does SQL Server generate the sequence of identity values that it assigns to the new identity field? If it follows the sequence in which the records were inserted to the table, then we are golden. :wink:

8

so how would an identity field help out in tracking changes. why not slap a change trigger on it and log that to an audit table with

column_name
before_change
current_change
changed_by
changed_date
9

Yes, it will. If multiple rows get inserted in one statement, any of those rows could get any of the next identity values; that is, without an ORDER BY on the INSERT, there's no way to know the specific order of identity values for rows all inserted in the same statement.

As to assigning identity values to existing rows, I suspect it will generally follow the current physical order in the table. That may or may not match the original INSERT order.

2 Likes
10

Scott, the rows are inserted to the log table one at a time only, because the changes are made by users through an app that allows them to edit the fields and save the changes one record at a time. So with that, I think we will be fine.

Thank you so much! :+1:

11

Because, let's say there are three changes with log ID = 1, 2, 3 (i.e., the first change is ID 1, the second change is ID 2, and the third change is ID 3). We can check the fields that changed from ID 1 to ID 2, and from ID 2 to ID 3 - in sequential order that the changes were made.

12

how about leveraging an audit column with datetime stamp? more reliable?

13

Cool. Yes, you are good if it's only one row at a time being INSERTed.

14

Yes, for the future. But now we have a table that we need to do a report on, and it's too late to add a date-time stamp.

15

Sorry for the bad news but, if there's currently no column that clearly defines the insert order for the existing rows in the table, there's nothing behind the scenes that will do it correctly for you.

1 Like