Okay this might come on a bit strong but anyway.
Are you kidding me?
Having a table without anything to uniquely identify a row... okay thats bad.
Okay you say lets create a primary key on all the columns. I wouldnt. All values in those column could change (and be NULL), adding FK to other tables to this (salery maybe) would require ALOT of redundant data... that could change. So no.
Now since this is a Emp_Details
. There should be something like an Employee table with hopefully something like EmpID. Now lacking that table a
alter table Emp_Details add i integer identity
alter table Emp_Details add constraint PK_Emp_Details primary key clustered (i)
Atleast to have something unique to identify a row.
Now to fix the duplicates all you need would be.
delete from Emp_Details
where i not in (select Min(i)
from Emp_Details e2
group by e2.Emp_name, e2.Company, e2.Join_Date, e2.Resigned_Date)
Now i admit that this IS SLOWER. It is however shorter then
delete from a
from (select Emp_Name, Company, Join_Date, Resigned_Date
,ROW_NUMBER() over (partition by Emp_Name, Company, Join_Date
order by Emp_Name, Company, Join_Date
from Emp_Details) a
where a.RowNumber > 1
And personally i think its easier to read. Now add a unique index to this table as well and this is a one time thing because the problem wont happen again. And then performance might not be that important (depending on the number of rows in the table off course)