You have to create a new table with same structure + identity column. Insert the old data into it with ordering

Insert into ...
Select ...
Order by ...

Otherwise you have to think about adding a column (not identity) . Fill it with auto numbers in the order you want like @Tim answer. Then set it as identity

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

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.

you can not update IDENTITY cloumn,you delete and insert

select Id,Name,Created into #temptable from [Person]
TRUNCATE TABLE [Person]
ALTER TABLE [Person] ADD [IdentityCol] INT IDENTITY(1,1) NOT NULL

INSERT INTO [Person](Id,Name,Created)
select Id,Name,Created from #temptable order by Created

This is what worked for me:

update p
    set IdentityCol = IdentityColumn
from
    Person p
    join (select Id, row_number() over (order by Created) IdentityColumn from Person p2) SQ on p.Id = SQ.Id

Then turn on the identity:

SET IDENTITY_INSERT database.schema.Person ON