Find missing and duplicate number from a sequence of numbers in SQL table

Sometimes it is important to know which values in a sequence are missing, either to find unused values so they can be used, or to find “holes” in the data. In this article I’ll show you how to find missing values.

CREATE TABLE [dbo].[test](
[custid] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[custid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

//---------------------------------------
Insert the following values in the custid.

custid
-------
1
2
3
4
6
7
8
9
10
15
16
17
18
19
20

To Find missing no the SQL query should be:

select m.custid +1 as start
from test as m
left outer join test as r on m.custid = r.custid - 1
where r.custid is null

Output-

Start
---------
5
11
21

Description:first replicate the table as m and r,then its finding the null value from r(where r.custid is null) and joining with the previous value of null i.e r.custid-1 with the m table.
first it finds 4 as 5 is not in the r table and join 4 with the row of m where m.custid contains 4,similarly for 10 and 20.then it returns m.custid+1.

To find the range of missing values:
select start, stop from (
select m.custid + 1 as start,
(select min(custid) - 1 from test as x where x.custid > m.custid) as stop
from test as m
left outer join test as r on m.custid = r.custid - 1
where r.custid is null
) as x
where stop is not null;

Start Stop
------------
5 5
11 14

you can rewrite this query (without subqueries) as follows.

select l.custid + 1 as start, min(fr.custid) - 1 as stop
from test as l
left outer join test as r on l.custid = r.custid - 1
left outer join test as fr on l.custid <> m.custid) as stop
from test as m
left outer join test as r on ascii(m.custid) = ascii(r.custid) - 1
where r.custid is null
) as x
where stop <> '';

How to find duplicate record in sql server:
select id, count(*) from sequence
group by id
having count(*) > 1;


Reference:http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/