Deleting duplicate rows in SQLServer is very simple.
Example : Check this table let me call it as Sample, order number 1,2 & 3 have identical values. Now we need to delete the duplicate rows.
Step 1 : Insert another column in table Sample called Dup of type Integer.
Step 2 : Execute this Query (uses Common Table Expression)
WITH DUP_CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ORDERNO,Name,COST ORDER BY ORDERNO) AS DupRank FROM Sample
UPDATE DUP_CTE SET Dup = DupRank
Now the table looks like this
Step 3 : Delete all the rows where Dup greater than 1
DELETE Sample Where Dup > 1
Step 4 : If new column Dup is not needed drop it from Table.
Easy right !!