Finding duplicate records from a table in sql server

 By using row_number() function :
;with dup as(select customer_id, firstname,lastname,row_number() over(partition by firstname,
lastname order by customer_id desc) as numOfDup from customer_details)
select * from dup where numOfDup > 1 order by customer_id

 By using dense_rank() function :
;with dup as(select customer_id,firstname,lastname,dense_rank() over(partition by firstname,lastname
order by customer_id desc) as numOfDup from customer_details)
select * from dup where numOfDup > 1 order by customer_id


 By using rank() function :
;with dup as(select customer_id,firstname,lastname,rank() over(partition by firstname,lastname
order by customer_id desc) as numOfDup from customer_details)
select * from dup where numOfDup > 1 order by customer_id

 By using self join()
select distinct cd1.customer_id,cd1.firstname,cd1.lastname from customer_details cd1
join customer_details cd2
on cd1.firstname=cd2.firstname
and cd1.lastname=cd2.lastname
and cd1.customer_id > cd2.customer_id order by cd1.customer_id

 By using sub query
SELECT * FROM customer_details
    WHERE customer_id NOT IN (SELECT MIN(customer_id)
    FROM customer_details
    GROUP BY FirstName, LastName)