将IDENTITY转换为数据类型tinyint的算术溢出错误 [英] Arithmetic overflow error converting IDENTITY to data type tinyint

问题描述

我正在设计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]

这篇关于将IDENTITY转换为数据类型tinyint的算术溢出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！