Removing Duplicates from a Table in SQL Server

Checking for Duplicates

On any version of SQL Server, you can identify duplicates using a simple query, with GROUP BY and HAVING, as follows:
DECLARE @table TABLE (data VARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')

SELECT  data
      , COUNT(data) nr
FROM    @table
GROUP BY data
HAVING  COUNT(data) > 1

Removing Duplicate Rows in SQL Server

 

DECLARE @table TABLE (data VARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')

SET ROWCOUNT 1
DELETE FROM @table WHERE data = 'duplicate row'
SET ROWCOUNT 0


get full reference from given link

https://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/