如何为 SQL Server 中的列添加标识? [英] How to add identity to the column in SQL Server?

问题描述

我有一个大约有 1000 万行的表.我们刚刚使用 SQL Server Management Studio 从另一个数据库导入它.它创建了表，但在主键列上没有标识和主键.

I have a table about 10 million rows. We just imported it from another database with SQL Server Management Studio. It creates table but without identity and primary key on primary key column.

我可以添加主键，但无法添加身份.当我在设计师中做这件事时，它总是超时.即使我将超时设置设置为 0.

I could add the primary key but couldn't add identity. It's all the time timing out when I'm doing it in designer. Even I set time out settings to 0.

我可能需要创建另一个列集主键和标识，从旧列复制数据，删除旧列并重命名新列.

I need to create probably another column set primary key and identity, copy data from old, delete old column and rename new one.

谁能告诉我，对于如此大的桌子，最好的方法是什么，而不会造成额外的过热?

Can anyone show me what will be the best way to do it for such big tables, without additional overheating?

推荐答案

好的.我能够将身份添加到具有 1000 万条记录的现有主列.我花了大约 30 分钟.

Okay. I was able to add identity to the existing primary column with 10 million records. Took me about 30 mins.

步骤:

1. 将数据库更改为单用户模式(以确保没有其他连接到数据库，可能导致锁定)

1. Change database to single user mode (to make sure no other connections to databse, can cause lock)

在设计器模式下打开表格

Open table in designer mode

做出改变.不保存

单击生成更改脚本"按钮(通常位于对象资源管理器上方的左侧)

Click Generate Change Script button (usually on the left right above the Object Explorer)

执行脚本

完成.现在您的列具有标识 :)

Done. Now your column has identity :)

这篇关于如何为 SQL Server 中的列添加标识?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！