sql - 我们如何使用自动生成的记录插入号在 sql server 中添加列。如果在该表上定义了索引

Question

我的数据库(SQL server 2008)已经有一些记录并且在两列上有两个排序索引。我尝试使用以下查询添加标识列。但它为我提供了与记录相关的错误身份号码。

alter table Table_name add RECORD_NUMBER int identity(1,1)  

首先插入的记录具有较大的编号，最后插入的记录具有较小的编号。有些记录的数字正好相反。是否可以消除此类问题并为记录插入正确的插入顺序。

我这样做是因为我的表中没有任何主键或唯一键或堆肥键。因此，为了识别首先输入的记录，我引入了身份列。我在想它会根据插入顺序分配记录号。

Answer 1

如果您可以在新表中移动数据，那么这是 Microsoft 基本上推荐的一种方法:

/*************

Preparing for the example, create tables, insert sample data,...

**************/
-- Your existing table
CREATE TABLE dbo.oldtable
(
    colA int,
    colB nvarchar(10)
)

-- The new table, same structure + new identity column:
CREATE TABLE dbo.newtable
(
    colID int IDENTITY(1,1),
    colA int,
    colB nvarchar(10)
)

--create unique clustered index on new identity column
create unique clustered index ucx_newtable 
on dbo.newtable (colID ASC)

--create other indexes as you need them
create index idx_newtable
on dbo.newtable (colA ASC)

--sample data
insert into dbo.oldtable(colA,colB) 
values (16,'this'),(17,'is'),(225,'an'),(300,'example')


/*************

Data Movement

**************/


BEGIN TRANSACTION

BEGIN TRY
    --move data to new table including new id column which is generated by
    --colA, which gives us the order from old to new in this example

    SET IDENTITY_INSERT dbo.newtable ON --So we can insert into the colID identity column

    INSERT INTO dbo.newtable(colID, colA,colB)
    SELECT  ROW_NUMBER() OVER(ORDER BY colA ASC) AS colID --this will generate numbers beginning from 1
            ,colA, colB
    FROM    dbo.oldtable

    SET IDENTITY_INSERT dbo.newtable OFF

    --rename old table / or drop it if you want
    EXEC sp_rename @objname = 'dbo.oldtable', @newname = 'dbo.oldtable_xyz'
    EXEC sp_rename @objname = 'dbo.newtable', @newname = 'dbo.oldtable'

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    ROLLBACK TRANSACTION
END CATCH