创建身份列需要多长时间？
我有一个包含 4000 万条记录的表。
什么是最好的（更快）？直接在该表中创建列，还是使用标识列创建另一个表并从第一个表插入数据？
如果我在有 4000 万条记录的表中创建一个标识列，是否可以估计创建它需要多长时间？
I have a table that have 40million records.
What's best (faster)? Create a column directly in that table or create another table with identity column and insert data from first?
If I create an identity column in the table that have 40million records, is it possible estimate how long does it take to create it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
这种要看情况。创建标识列不会花费那么长时间（好吧，这与表的大小有关），假设您将其附加到表的末尾。如果没有，服务器必须创建一个新表，并将标识列放在所需位置，将所有行导出到新表，然后更改表名称。我猜这就是花了这么长时间的原因。
This kind of depends. Creating an identity column won't take that long (well ok this is relative to the size of the table), assuming you appended it to the end of the table. If you didn't, the server has to create a new table with the identity column at the desired position, export all the rows to the new table, and then change the table name. I am guessing that is what is taking so long.
我猜它被阻止了 - 您是否使用了 GUI 或查询窗口（您知道它运行的 SPID 吗？）
尝试这些 - 让我们知道它们是否给出结果，而您不确定该怎么做：
I'm guessing it's blocked - did you use the GUI or a query window (do you know the SPID it's running under?)
Try these - let us know if they give results and you're not sure what to do:
如果您在查询窗口中使用
ALTER TABLE [...] ADD ...，速度会非常快，事实上它早就完成了。如果您使用 Management Studio 表设计器，它会将表复制到新表中，删除旧表，然后将新表重命名为旧表。这将需要一段时间，特别是如果您没有预先增长数据库和日志来容纳所需的额外空间。因为都是一笔交易，如果现在停止的话，回滚还需要大约16个小时。If you used
ALTER TABLE [...] ADD ...in a query window, it is pretty fast, in fact it would have finished long ago. If you used the Management Studio table designer it is copying the table into a new table, dropping the old one, then renaming the new table as the old one. It will take a while, specially if you did not pre-grow the database and the log to accommodate the extra space needed. Because is all one single transaction, it would take about another 16 hours to rollback if you stop it now.这不是你只需要做一次的事情吗，因此需要多长时间并不是真正的问题？ （假设不需要几天...）
Isn't it something you'll only have to do once, and therefore isn't really a problem how long it takes? (Assuming it doesn't take days...)
您不能创建数据库的测试副本并在其上创建列来查看需要多长时间吗？
Can you not create a test copy of the database and create the column on that to see how long it takes?
我认为很大程度上取决于硬件以及您所在的 DBMS。在我的环境中，创建一个新表并将旧数据复制到其中大约需要 3 或 4 小时。根据其他经验，我预计添加身份列大约需要相同的时间。我使用的是在 SAN 上有多个服务器的 Oracle，因此运行速度比在单个服务器环境中更快。您可能只需要坐下来等待。
I think a lot depends upon the hardware and which DBMS you are in. In my environment, creating a new table and copying the old data into it would take about 3 or 4 hours. I would expect the addition of an identity column to take around the same amount of time, just based on other experiences. I'm on Oracle with multiple servers on a SAN, so things can run faster than in a single server environment. You may just have to sit back and wait.