/*--------------------------------------------------------------------------------
SAMPLE CODE ONLY BELOW:
--------------------------------------------------------------------------------*/

-- Create the table
if object_id('dbo.t1') > 0
drop table dbo.t1;
go
create table dbo.t1 (pkid int identity, charcol varchar(20));
go

/*--------------------------------------------------------------------------------
BEGIN - data setup
Insert data with multiple gaps, some with a single value gap, some with 
multiple value gaps, and some with double-digit gaps
--------------------------------------------------------------------------------*/

-- Insert the first group
insert dbo.t1 (charcol) select '1111';
insert dbo.t1 (charcol) select '1111';
insert dbo.t1 (charcol) select '1111';

-- Gap, multiple values
declare @i int
set @i = 4
while @i < 7 begin
begin transaction;
	insert dbo.t1 (charcol) select 'rollback';

	rollback transaction;

	select @i = @i + 1
end
go

-- Insert second group
insert dbo.t1 (charcol) select '2222';

-- Gap, single value
begin transaction;
	insert dbo.t1 (charcol) select 'rollback';
	rollback transaction;
go

-- 3rd insert group
insert dbo.t1 (charcol) select '3333';
insert dbo.t1 (charcol) select '3333';
insert dbo.t1 (charcol) select '3333';
insert dbo.t1 (charcol) select '3333';
insert dbo.t1 (charcol) select '3333';

-- Gap, double-digit values
declare @i int
set @i = 14
while @i < 26 begin
begin transaction;
	insert dbo.t1 (charcol) select 'rollback';
	rollback transaction;
	select @i = @i + 1
end
go

-- 4th insert group
insert dbo.t1 (charcol) select '4444';

-- Gap, single value
begin transaction;
	insert dbo.t1 (charcol) select 'rollback';
	rollback transaction;
go

-- 5th insert group
insert dbo.t1 (charcol) select '5555';
insert dbo.t1 (charcol) select '5555';
insert dbo.t1 (charcol) select '5555';

-- Gap, multiple values
declare @i int
set @i = 31
while @i < 35 begin
begin transaction;
	insert dbo.t1 (charcol) select 'rollback';
	rollback transaction;
	select @i = @i + 1
end
go

-- Final value
insert dbo.t1 (charcol) select '6666';
go


-- View the data order by the identity

-- value, should look like the output

-- below the statement

select * from dbo.t1 order by pkid;

/*

pkid charcol
----------- --------------------
1 1111
2 1111
3 1111
7 2222
9 3333
10 3333
11 3333
12 3333
13 3333
26 4444
28 5555
29 5555
30 5555
35 6666
*/



/*--------------------------------------------------------------------------------
END - data setup
--------------------------------------------------------------------------------*/


-- View the gaps using a solution that only provides the
-- minimum value for each gap range - you'll notice you
-- never see the values like 5,6,15,16,17,etc. in this
-- result
SELECT a.pkid + 1 as GapValue
FROM t1 a
WHERE NOT EXISTS( SELECT * FROM t1 b
WHERE b.pkid = a.pkid + 1 )
AND a.pkid < (select max(pkid) from t1);
go

-- Use a recursive CTE approach, which will provide
-- all values in all gap ranges
declare @i int;
SELECT @i = MAX(pkid) FROM t1;
WITH tmp (gapId) AS (
SELECT DISTINCT a.pkid + 1
FROM t1 a
WHERE NOT EXISTS( SELECT * FROM t1 b
WHERE b.pkid = a.pkid + 1)
AND a.pkid < @i

UNION ALL

SELECT a.gapId + 1
FROM tmp a
WHERE NOT EXISTS( SELECT * FROM t1 b
WHERE b.pkid = a.gapId + 1)
AND a.gapId < @i
)
SELECT gapId
FROM tmp
ORDER BY gapId;
Go