清空表数据并重置IDENTITY列 [英] Empty table data and reset IDENTITY columns

问题描述

我在SQL Server中创建了一个有几个表的数据库。我运行一些测试，现在我准备部署我的解决方案，问题是，表中有各种各样的数据。我想删除使用我的测试创建的所有表的每一行，并将主键还原为零。

I created a database in SQL Server with a couple of tables. I ran some tests and now am ready to deploy my solution, problem is, there is all sorts of data in the tables. I want to delete every row of all the tables created with my tests and put back the primary keys to zero. I tried delete which doesn't reset the primary keys index, and drop simply destroyed the table.

推荐答案

您可以尝试 TRUNCATE TABLE ，它删除所有行并重置标识种子。然而，如果你有外键，你将必须以一定的顺序执行。在这种情况下，您需要先从子表中删除，或删除约束并重新添加。

You can try TRUNCATE TABLE which deletes all rows and resets identity seeds. However you will have to execute in a certain order if you have foreign keys. In which case you will need to delete from the child tables first, or drop the constraints and re-add them.

还要注意，如果使用 IDENTITY 你真的不应该期望数字意味着什么，或永远生成没有差距。你不应该关心它是从1对22还是65开始 - 你能解释为什么身份值需要重置吗？

Also note that if you are using IDENTITY you really shouldn't expect the numbers to mean anything, or to be generated forever without gaps. You shouldn't care whether it starts at 1 vs. 22 or 65 - can you explain why the identity values need to be reset?

这篇关于清空表数据并重置IDENTITY列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！