What happens if an autoincrementing primary key goes out of range when inserting a new row, but there are unused gap values available?
I have a table (not designed by me) that has a smallint auto incrementing primary key, i.e. IDENTITY(1,1):
CREATE TABLE [table1]( [id] [smallint] IDENTITY(1,1) NOT NULL, ... CONSTRAINT [id_pk] PRIMARY KEY CLUSTERED ([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
I'm trying to figure out what happens when the primary key value goes out of range, but there are gaps in the table that can be filled. For example, suppose the table has 32767 rows (max smallint value), with ids spanning from 1 to 32767. Now suppose I delete half of the rows and try to insert a new row - will an out-of-range error occur, or will ids somehow be recalculated and allow a new record insertion (given that 32767+1 cannot be used as a new id value)?
From what I've found, gap values are (for good reasons) not supposed to be re-used. But does that mean (in an extreme case) that if there is only one row in the table with id=32767 (e.g. other rows have been deleted), trying to insert another row will cause problems due to out of range PK value? Or will SQL Server handle such situations (and assign some other id value to the new row) - meaning that 32767 can be seen merely as the maximum number of rows in the table? How can such situations be handled when they occur, and changing the table design (i.e. from smallint to bigint or guid as a PK) is not possible?
IDENTITY values produce an error if they go out of range. The only remedy in this case is to either reseed the value (DBCC CHECKIDENT('MyTable', RESEED, 0)) or expand the datatype to a bigger one.
Why does SQL Server not reuse gaps and why do gaps exist in the first place? That has performance reasons. Internally an IDENTITY is a variable in memory that can be updated atomically. That's important because multiple threads may insert into a given table with an IDENTITY.
Imagine thread one inserts into table 1, and it is given id = 10. The threads transaction continues with other work. Now thread two also inserts into the same table concurrently (that is possible unless you use SERIALIZABLE) and is given id = 11. Thread one now runs into an error and its transaction is rolled back, freeing id 10. You cannot change thread two's id of 11 retroactively, so you have a gap.
If you want to fill that gap, you have to know that it's there, but SQL Server simply does not store that information. Even if it would, you'd have all kinds of locking issues arising which are neatly avoided with the single atomic value that is used as the counter.
In SQL Server 2012 you can even use such atomical counters in your own code directly through the SEQUENCE keyword.
From the documentation:
Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.
So the gaps will remain, and the insert will fail.
For proof, run the following code:
DROP TABLE table1 CREATE TABLE [table1]( [id] [smallint] IDENTITY(1,1) NOT NULL, [somecolumn] varchar(10), CONSTRAINT [id_pk] PRIMARY KEY CLUSTERED ([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] SET IDENTITY_INSERT table1 ON INSERT INTO table1 (id, [somecolumn]) VALUES (32766, 'a') SET IDENTITY_INSERT table1 OFF INSERT INTO table1 ([somecolumn]) VALUES ('b') -- Following line will print error: --Msg 8115, Level 16, State 1, Line 16 --Arithmetic overflow error converting IDENTITY to data type smallint. INSERT INTO table1 ([somecolumn]) VALUES ('c')
This depends on the Storage Engine you use. MyISAM would handle this request differend to InnoDB for example. Mostly you should get an SQL error when trying to write into your DB and there is no Primary Key value left.
Refering to : http://dev.mysql.com/doc/refman/5.1/en/integer-types.html smallint will be 65535 when unsigned