如何重新编号标识列 [英] how to renumber identity column

问题描述

删除任何行时如何重新编号标识列

how to renumber identity column when any row is deleted

推荐答案

DBCC CHECKIDENT ("table1", RESEED, 300)

使用以上方法设置起始索引号。
在执行上述查询后插入时，
300将成为起始索引编号

use the above to set the starting index number.
300 will be the starting indexed number when you insert after executing the above query

AS Griff严格指出，请尽量避免这种情况。



我真的需要这个，请在​​SQL Server中使用ReSeed funtinality



这些链接将回答你的问题



1. Sql-server-reseed-identity-of-table-table-missing-identity-values-gap-in-identity-column ]


2。 Sql-server-delete-truncate-and-reseed-identity

AS Griff rigtly pointed out, please try to avoid this scenarion.

I you really need this, please use the ReSeed funtinality in SQL Server

These links will answer your questions

1. Sql-server-reseed-identity-of-table-table-missing-identity-values-gap-in-identity-column]

2. Sql-server-delete-truncate-and-reseed-identity

简单规则：不要。



这将导致更多问题解决。问题是ID应该是唯一的 - 所以当你删除一行时，你必须重新编号所有具有更高数字的行。如果该ID在另一个表中用作外键怎么办？所有这些都必须重新编号 - 包括加载到应用程序的其他实例的内存中的任何记录，或使用相同数据库的任何其他应用程序。如果你从不同的用户同时得到两个删除怎么办？新的数字序列应该是什么？



如果你想要序列号，还有其他解决方案 - 但不要试图破坏身份字段，因为它会导致很多生产中可能不明显的间歇性问题。

Simple rule: Don''t.

It will cause more problems that it will solve. The problem is that it is intended that the ID should be unique - so when you delete a row, you would have to renumber all the rows with higher numbers. What if that ID is being used in a different table as a foreign key? All of those must be renumbered as well - including any records which are loaded into the memory of other instances of your application, or any other application using the same database. And what if you get two deletes at the same time from different users? What should the new number sequence be?

If you want sequential numbers, there are other solutions - but do not try to subvert an identity field as it will cause a lot of intermittent problems in production that may not be apparent in development.

这篇关于如何重新编号标识列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！