Alter table to add Identity column based on Order By

Question

I have this table

Person

------------------------------------
| **Id**  |**Name**  | **Created** | 
------------------------------------
| Guid1   | John Doe |  2016-01-01 |
------------------------------------
| Guid2   | Mary Jane|  2015-01-01 |
------------------------------------

Column Id is the PK and a Clustered Index, so I want to add an Identity Column:

ALTER TABLE [Person]
ADD
IdentityCol INT IDENTITY(1,1) NOT NULL

The problem is that every time when I try to do this, it adds the value of IdentityCol, based on the default order of the table, which I suppose is defined by the column Id

Person

-------------------------------------------------
| **Id**  |**Name**  | **Created** | IdentityCol|
-------------------------------------------------
| Guid1   | John Doe |  2016-01-01 |    1       |
-------------------------------------------------
| Guid2   | Mary Jane|  2015-01-01 |    2       |
-------------------------------------------------

Buy what I want is that create the values order by Created column

-------------------------------------------------
| **Id**  |**Name**  | **Created** | IdentityCol|
-------------------------------------------------
| Guid1   | John Doe |  2016-01-01 |    2       |
-------------------------------------------------
| Guid2   | Mary Jane|  2015-01-01 |    1       |
-------------------------------------------------

Is there any way to specify the Order By in ALTER sentence?

Answer 1

I don't know if this is the correct way to solve my problem, but I ended with something like this:

First, I remove the index created by SQL for PK (Column Id).

ALTER TABLE [Person] DROP CONSTRAINT PK__PersonId

This works only if you don't have any table related to this by the column Id, and removes at the same time the PK of Person Table.

Second, I create a temporal clustered Index on Created column:

CREATE CLUSTERED INDEX IX_CreatedTemp ON [Person] (Created ASC)

Now SQL sort the table based on the Created column, so now I can add my Identity column.

ALTER TABLE [Person]
    ADD IdentityCol INT UNIQUE IDENTITY(1,1) NOT NULL

When the column is added, SQL automatically fills the value based on the default order, which is determined by the Index IX_CreatedTemp.

So the result is as I expected

** Person **

-------------------------------------------------
| **Id**  |**Name**  | **Created** | IdentityCol|
-------------------------------------------------
| Guid1   | John Doe |  2016-01-01 |    2       |
-------------------------------------------------
| Guid2   | Mary Jane|  2015-01-01 |    1       |
-------------------------------------------------

Now is time to return the column Id as the PK. First, I remove my temp Index:

DROP INDEX  IX_CreatedTemp ON [Person]

And now add the PK:

ALTER TABLE [Person] 
    ADD CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (Id)

Finally, I added a new index based on my new IdentityCol to make my table faster

CREATE UNIQUE CLUSTERED INDEX IX_IdentityCol ON [Person] (IdentityCol ASC)

And everything is working fine.

Answer 2

Use row number:

UPDATE Person p
SET IdentityCol = (SELECT ROW_NUMBER() over (ORDER BY Created) AS rn
                   FROM Person t WHERE p.Id = t.Id)

Then you could set the IdentityCol as a formal identity column via:

SET IDENTITY_INSERT database.schema.Person ON