在SQL 2005中，如果自动编号列的编号用完了怎么办? [英] What happen in SQL 2005 when it run out of number for an autonumber column?

问题描述

当SQL Server 2005恰好达到IDENTITY列的最大值时会发生什么?它是否从头开始并开始填补空白?

What happen when SQL Server 2005 happen to reach the maximum for an IDENTITY column? Does it start from the beginning and start refilling the gap?

SQL Server 2005发生时的行为是什么?

What is the behavior of SQL Server 2005 when it happen?

推荐答案

达到最大值时，您将收到溢出错误.如果您使用bigint数据类型的最大值为9,223,372,036,854,775,807，则将极有可能不会出现这种情况.

You will get an overflow error when the maximum value is reached. If you use the bigint datatype with a maximum value of 9,223,372,036,854,775,807 this will most likely never be the case.

您将收到的错误消息如下所示:

The error message you will get, will look like this:

Msg 220, Level 16, State 2, Line 10
Arithmetic overflow error for data type tinyint, value = 256.

(源)

据我所知，MS SQL没有提供任何功能来填补身份空白，因此您要么必须自己做，要么更改身份列的数据类型.

As far as I know MS SQL provides no functionality to fill the identity gaps, so you will either have to do this by yourself or change the datatype of the identity column.

除此之外，您可以将起始值设置为最小的负数，以获取更大范围的值来使用.

In addition to this you can set the start value to the smallest negative number, to get an even bigger range of values to use.

这是一篇关于该主题的好博客文章.

这篇关于在SQL 2005中，如果自动编号列的编号用完了怎么办?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！