select name from emp group by name HAVING COUNT(*) > 1

Please refer the article Remove duplicate records/data from Sql Server database table http://www.webcodeexpert.com/2013/11/how-to-remove-duplicate-recordsdata.html

select id, count(*) as numberduplicate from TableName group by id having count(*) >1 order by id

select COLUMNNAME from TABLENAME group by COLUMNNAME having COUNT(COLUMNNAME )>1

I agree with Sandesh's answer

with x as (select *, rn=ROW_NUMBER()over(PARTITION BY username, userrole order by userid) from t_user )select * from x where rn>1