更改表以根据 Order By 添加 Identity 列

问题描述

我有这张桌子

人

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

列 ID 是 PK 和聚集索引，所以我想添加一个标识列:

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

问题是，每次我尝试这样做时，它都会根据表的默认顺序添加 IdentityCol 的值，我认为它是由列 Id 定义的

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

人

-------------------------------------------------
| **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       |
-------------------------------------------------

有什么办法可以在 ALTER 语句中指定 Order By 吗?

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

推荐答案

使用行号:

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

然后您可以通过以下方式将 IdentityCol 设置为正式身份列:

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

SET IDENTITY_INSERT database.schema.Person ON