问题描述
我有这张桌子
人
------------------------------------ | **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