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

2021-09-10 00:00:00 sql tsql sql-server

我有这张桌子

人

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

相关文章