問題描述

我有這張桌子

人

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

這篇關于更改表以根據 Order By 添加 Identity 列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！