重置主键(int 作为身份) [英] Reset primary key (int as identity)

问题描述

我有一个主键为 bigint 的表(身份属性是 Yes 并且从 1 x 1 开始).此表正在生产中，并且每天更新:大量删除和插入.

I have a table with a primary key as bigint (identity property is Yes and staring from 1 by 1). This table is in production and has been updated on daily bases: lots of deleting and inserting.

问题是这个密钥现在有 8 位数字变得太大了.我担心有一天会溢出.

The problem is that this key is growing too big right now with 8 digits. I worry about overflow one day eventually.

幸运的是，这个键没有用作任何其他表的外键.它仅用于标识表中的一行.因此，我可以安全地重新设置从 1 开始的键值，也许每年一次.

Fortunately, this key is not used as foreign keys to any other tables. It is just used to identify a row in the table. Therefore I can safely reset the key values starting from 1 again, maybe once a year.

我可以创建一个空白表并在那里复制其他字段数据，然后删除原始表中的所有行，重置键/表，最后将数据复制回来.

I could create a blank table and copy other field data there, then remove all the rows in the original table, reset the key/table and finally copy data back.

不确定 Microsoft SQL 2005 中是否有可用的内置 sp_xxx 来完成这项工作:只是从 1 开始按顺序重置主键而不影响其他列数据?或者任何其他简单的解决方案?

Not sure if there is if there is a build-in sp_xxx available in Microsoft SQL 2005 to do the job: just to reset primary key in sequence starting from 1 without affecting other column data? Or any other simple solution?

推荐答案

bigint 的最大值是 9,223,372,036,854,775,807.如果您在一天内达到 8 位数，您仍然需要 10¹¹ 天才能达到最大值.这就像 2500 万年.

The maximum value for a bigint is 9,223,372,036,854,775,807. If you'd gotten to 8 digits in a day you'd still need 10¹¹ days to hit the max. That's like 25 million years.

假设您仍然想重置列，我的第一个问题是:行的顺序重要吗?这意味着您是否依赖于第 1000 行在 1100 之前出现的事实，例如，按时间顺序或其他绝对顺序?如果没有，这很简单:删除该列并再次添加它.你好，新价值观.

Assuming you still want to reset the column, the first question I have is: is the ordering of rows important? Meaning do you rely upon the fact that row 1000 comes before 1100 for, say, chronological or otherwise absolute ordering? If not, it's easy: delete the column an add it again. Hey presto, new values.

如果您需要维护订单，则需要更加小心:

If you need to maintain the order you'll need to do it a little more carefully:

1. 锁住桌子；
2. 更改类型，使其不再自动递增；
3. 创建一个新列.你最好暂时让它没有索引，因为更新索引会减慢插入速度；
4. 用某种循环填充第二个中的值，增加计数器(如 SQL Server rownum 技巧)，对插入进行排序以匹配原始顺序；
5. 用新列替换旧列；
6. 重置自动递增和主键状态.

这篇关于重置主键(int 作为身份)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！