按訂單條件添加自動增量列

我有一個沒有自動遞增 id 列的大表。我想添加此列，但我想按定義為日期的列的順序添加。

什麼應該是最實用的方法？比如我可以按照我想要的順序把數據傳到一個臨時表中，然後按照那個順序給這個表添加一個自增的id列，然後和另一個表匹配嗎？（我不認為這是一種有效的方法。）MSSQL 中是否有特殊的功能或命令塊或方法，我該如何做到這一點？

身份列有一些特殊的規則，所以為了實現你的目標，我相信你必須採取一些迂迴的步驟：

創建一個與現有表的架構匹配的新表。您可以使用SELECT INTO如下語句來縮短此步驟：SELECT TOP 0 * INTO YourTableNew FROM YourTable;. 請注意，這不會保留相同的約束或外鍵引用，因此如果您有任何這些，您也必須手動添加它們。

ALTER TABLE YourTableNew ADD SomeIdentityColumn INT IDENTITY(1,1);這會將新的標識列添加到您的新表中。

SET IDENTITY_INSERT YourTableNew ON;這允許您將值插入標識列。

使用 CTE 和視窗函式生成您的身份值並插入到您的新表中，如下所示：

WITH _IdentityValues AS
(
   SELECT 
       Column1,
       Column2, -- etc, all of your columns
       ROW_NUMBER() OVER (ORDER BY TheDateColumn) AS SomeIdentityColumn
   FROM YourTable
)

INSERT INTO YourTableNew (Column1, Column2, SomeIdentityColumn)
SELECT Column1, Column2, SomeIdentityColumn
FROM _IdentityValues;

請注意，如果其中TheDateColumn有重複的值，那麼dupe 首先出現的函式YourTable將會有一個不確定的分配。ROW_NUMBER()

將身份規範重新設置為正確的新值：（DBCC CHECKIDENT ('[TestTable]', RESEED, 9999);將 9999 替換為目前最大值YourTableNew加 1。）

關閉身份插入：SET IDENTITY_INSERT YourTableNew OFF;。

如果您關心保留原始表名，請使用sp_rename以下方式切換錶名：

EXEC sp_rename 'YourTable', 'YourTableOld';
EXEC sp_rename 'YourTableNew', 'YourTable';

另一種方法是使用IDENTITY函式。更多資訊在這裡

這是我創建的一個測試供您參考。請注意，正如JD提到的，您仍然必須使用sp_rename來切換錶，如果表中start_date有重複的值，那麼將有一個不確定的IDENTITY值分配

IF OBJECT_ID('dbo.testident', 'U') IS NOT NULL 
 DROP TABLE dbo.testident; 

DECLARE @table table (NAME varchar(100), START_DATE date)

INSERT INTO @table 
SELECT 'Biju',GETDATE()+5
UNION ALL
SELECT 'Rick',GETDATE()+1
UNION ALL 
SELECT 'Sam' ,GETDATE()+2

SELECT * FROM @table

SELECT id=IDENTITY(BIGINT,1,1),
      name,
      START_DATE 
 INTO testident
 FROM @table
ORDER BY START_DATE asc

SELECT * 
 FROM testident

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.testident'),'id','IsIdentity') is_identity

以下是上述查詢的結果

結果集 1 原始數據

+------+------------+
| NAME | START_DATE |
+------+------------+
| Biju | 2022-01-31 |
| Rick | 2022-01-27 |
| Sam  | 2022-01-28 |
+------+------------+

結果集 2具有基於生成的標識的新表start_date

+----+------+------------+
| id | name | START_DATE |
+----+------+------------+
|  1 | Rick | 2022-01-27 |
|  2 | Sam  | 2022-01-28 |
|  3 | Biju | 2022-01-31 |
+----+------+------------+

結果集 3檢查創建的新列是一IDENTITY列。

+-------------+
| is_identity |
+-------------+
|           1 |
+-------------+

引用自：https://dba.stackexchange.com/questions/306601