I have got a query about gaps in values of identity column and error messages related to duplication in values of identity column. The problem may be divided in two parts Gaps in identity column values and Duplication error messages related to seed property. Let me discus these one by one
Gaps in identity column values
Gaps in values of identity columns are generated by failed inserts or successful delete operations on table. It has no considerable negative impact but may be removed for optimized usage of identity column data type. For example if there is an identity column with data type tinyint then after reaching value of 255 the column would not be able to hold further rows. Same is true for other identity data types like int, bigint, smallint, decimal or numeric. In such circumstances removing the gaps among values of identity column may optimize the usage of identity column data type.
A simple way to this task may be to drop and re-create the identity column with same name and properties. When re-created it would be optimized and have no gaps among values. Performing drop and re-create through T-SQL would be much more efficient than through SSMS (have a look at my article on ALTER TABLE operations through T-SQL and SSMS). But through T-SQL you would not be able to re-create the column at any position in table but last. So if you have used select * any where in application code (that i always try to avoid), then change of column order may raise issues. In that case you may use SSMS to delete and re-create the identity column at required position in table. Consider the following example for above process
-- Create table for test
if exists (select * from sys.objects where name = 'IDTest' )
drop table IDTest
IDTest(ID smallint primary key identity (1,1), name varchar(50))
-- Populate the table
insert into IDTest values ('Atif ')
-- Delete records
delete from IDTest where id between 11 and 60
-- Verify the gap created by delete operation
select * from IDTest order by ID
We may look that gap exists in values of identity column after delete operation. Now we may apply the drop and re-create approach for identity column to remove the gaps and make the column optimized. For task through SSMS open table in designer, delete the identity column and then create it again with same properties by using insert column option.
I have used T-SQL to drop and recreate the identity column.
-- Drop the primary key constraint first (do not forget to change the constraint name).
ALTER TABLE IDTest DROP PK__IDTest__3214EC2720C1E124
-- drop the identity column
ALTER TABLE IDTest DROP COLUMN ID
-- Re-create the identity column
ALTER TABLE IDTest ADD ID SMALLINT PRIMARY KEY IDENTITY (1,1)
At this point identity column ID is created and populated without any gap. But it would be created as last column with respect to columns order in table.
Duplication error messages related to seed and incremental value
Going back to second problem, it relates to SEED property of identity column. If after manipulating seed value, errors are being generated for duplication then follow these steps.
- Get maximum value in identity column
- ReSeed by providing the maximum value
It should be kept in mind that value retrieved through max() function may not necessarily be the maximum identity value. That may be the case when some rows have been deleted with last (highest) identity values. However setting the max value as SEED would work fine and no duplication issue would be raised.
-- Get last identity value
SELECT Max(ID) from IDTest
As we have 250 as max value in our re-created identity column so use it for RESEED
-- Get last identity value
DBCC CHECKIDENT ('IDTest', RESEED, 250)
In this way duplication due to invalid SEED value may be prevented. It is considerable that DROP and RE-CREATE of identity column may also solve duplication error issue.