Find Duplicate Records using CTE (Common Table Expression) in SQL Server 2005

There are many ways to find duplicate records in a table.Here i use CTE (Common Table Expression) for finding duplicate records.This is the simplest and easily understandable way.



CREATE TABLE [dbo].[Employees]
( [EmpID] int,
[EmpName] nvarchar(100)
)
GO
INSERT INTO [Employees]([EmpID], [EmpName])
select 1,'Aamir'
UNION ALL
select 2,'Faisal'
UNION ALL
select 3 ,'Yasir'
UNION ALL
select 4 ,'Aamir'
UNION ALL
select 5,'Jibran'
UNION ALL
select 6,'Arif'
UNION ALL
select 7,'Jibran'
UNION ALL
select 8,'Khalid'
select * from [Employees];
WITH DuplicateEmployees(EmpID,EmpName,Ranking)
AS
(
SELECT EmpID,EmpName,Ranking = DENSE_RANK() OVER(PARTITION BY EmpName ORDER BY NewId() ASC)
FROM [Employees]
)
select * from DuplicateEmployees
WHERE Ranking > 1