Use generic syntax for finding gaps in identity values This example shows generic syntax for finding gaps in identity values when data is removed. Note The first part of the following Transact-SQL script is designed for illustration purposes only. You can run the Transact-SQL script that starts with the comment: - - Create the img table. -- Here is the generic syntax for finding identity value gaps in data. -- This is the beginning of the illustrative example. SET IDENTITY_INSERT tablename ON DECLARE @minidentval column_type DECLARE @nextidentval column_type SELECT @minidentval = MIN(IDENTITYCOL) FROM tablename IF @minidentval = IDENT_SEED('tablename') SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('tablename') FROM tablename t1 WHERE IDENTITYCOL BETWEEN IDENT_SEED('tablename') AND MAX(column_type) AND NOT EXISTS (SELECT * FROM tablename t2 WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('tablename')) ELSE SELECT @nextidentval = IDENT_SEED('tablename') SET IDENTITY_INSERT tablename OFF -- Here is an example to find gaps in the actual data. -- The table is called img and has two columns: the first column -- called id_num, which is an increasing identification number, and the -- second column called company_name. -- This is the end of the illustration example. -- Create the img table. -- If the img table already exists, drop it. -- Create the img table. IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'img') DROP TABLE img GO CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname) INSERT img(company_name) VALUES ('New Moon Books') INSERT img(company_name) VALUES ('Lucerne Publishing') -- SET IDENTITY_INSERT ON and use in img table. SET IDENTITY_INSERT img ON DECLARE @minidentval smallint DECLARE @nextidentval smallint SELECT @minidentval = MIN(IDENTITYCOL) FROM img IF @minidentval = IDENT_SEED('img') SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('img') FROM img t1 WHERE IDENTITYCOL BETWEEN IDENT_SEED('img') AND 32766 AND NOT EXISTS (SELECT * FROM img t2 WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('img')) ELSE SELECT @nextidentval = IDENT_SEED('img') SET IDENTITY_INSERT img OFF