following method worked out for me

for this example i have used table called table1 which has single column column1.

create table table1

(column1 int)

I have inserted values 1,2,3,5,9 in to this table using following query

insert into table1

select 1 union all

select 2 union all

select 3 union all

select 5 union all

select 9 union all

select 10

In the above sequence 0,4,6,7,8 are missing. to find out missed values i have used following method

1. create a temporary table temp_table(later we can drop it)

create table temp_table

(column2 int)

2. following query will insert all the values from 0 to 10 into temp_table

DECLARE @val1 int;

set @val1 = 0;

while (@val1 < (select max(column1) from table1))

begin

insert into temp_table

select @val1

set @val1 = @val1+1

end

3. use exept function of SQl server 2005 to get missed values

select * from temp_table

except

select * from table1

4.delete temp_table