将 IDENTITY 转换为数据类型 tinyint 的算术溢出错误

2022-01-16 00:00:00 sql tsql sql-server auto-increment sql-server-2008-r2

我正在 SQL Server 2008 R2 SP2 Express 数据库中设计表.我创建了带有 nvarchar 列的数据和 tinyint 列用于自动递增标识的表，假设行不会超过几行(这就是我选择 0-255 的原因tinyint).当我添加超过 255 行时，即使在我删除该表中的所有行并尝试添加一个新行之后，此错误也会永久发生.我正在使用 SQL Server Management Studio Express 执行此操作.

I am designing table in SQL Server 2008 R2 SP2 Express database. I created table with nvarchar column for data and tinyint column for auto-incrementing identity assuming there will be no more than few rows (that's why I choose 0-255 tinyint). When I add more than 255 rows, this error keeps occurring permanently even after I delete all rows in that table and try to add one new row. I am doing this using SQL Server Management Studio Express.

  1. 如何强制数据库引擎检查哪些索引是空闲的?
  2. 如果我将 tinyint 更改为 int 并达到 int 数量的限制，也会发生这种情况吗?
  1. How to force database engine to check what indexes are free?
  2. Will this happen too if I change tinyint to int and reach limit of int number?

推荐答案

一旦你添加了255行，然后想从表中删除所有行，但是增量列的计数器已经设置为255，即使删除了所有行.

Once you add 255 rows, then want to delete all rows from table, But the counter of increment column is already set to 255, even after delete all rows.

要解决这个问题，您需要在删除所有行后重置增量列的计数器.请在删除所有行时使用以下查询，以便计数器重置为 0.执行此查询后，您可以在表中添加行，增量列值为 1 或您在表设计时设置的值.

To solve this, you need to reset counter of increment column after deleting all rows. Please use below query when deleting all rows, so that counter will reset to reset to 0. After execute this query, you can add rows in table with increment column values as 1 or what you set at table design time.

DELETE FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO

如果你想从表中删除所有行，你也可以使用 truncate table 命令.截断命令还将增量列重置为初始值.

You can also use truncate table command, if you want to delete all rows from table. Truncate Command also reset increment column to initial value.

Truncate Table [TestTable]

相关文章