Delete Remove duplicate records or rows in sql server

==>> Some time we need Delete or Remove the duplicate rows in sql server this type of problem when occur our data table does not contain any primary key column because of that it contains duplicate records that would be like this:----

:- For get duplicate records you need to run this query this query return how many records are multiple  The RowNumber column indicate the number of rows .......


SQLQuery:-

 WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY EName,dptName ORDER BY EName) As RowNumber,* FROM dbo.EMP
)
SELECT * FROM tempTable


Result:-

:-Now You See we having the two row are multiple in RowNumber column  greater then 1 mean the row in this table exist more then one time.

Now we need to execute another query for unique value from datatable for that we need write sql query like :-----


SQLQuery:-

 WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY EName,dptName ORDER BY EName) As RowNumber,* FROM EMP
)
DELETE FROM tempTable where RowNumber >1
SELECT * FROM EMP order by EId asc 




Result:-

So in this way we can remove the duplicate rows from sql table .

Hope this will help you for find your solution.