Find and Remove Duplicate Rows in a Table using SQL Server

Syntax:-

--//synatx to find duplicate and count the no of rows
;WITH CTE AS
(
    SELECT ColomnName, ROW_NUMBER() OVER
    (
        PARTITION BY ColomnName ORDER BY ID
    ) RowNumber
    FROM  TableName
)

--// to check duplicate record select command
SELECT *   FROM CTE WHERE RowNumber > 1

--//Delete duplicate  record
DELETE FROM CTE WHERE RowNumber > 1


Example:-

i have the table that contain the duplicate sector

Syntax to select the duplicate record:-

;WITH CTE AS
(
    SELECT name, ROW_NUMBER() OVER
    (
        PARTITION BY name ORDER BY id
    ) RowNumber
    FROM  dbo.Sectors
)


SELECT *   FROM CTE WHERE RowNumber > 1

Syntax to delete the duplicate Data:-

;WITH CTE AS
(
    SELECT name, ROW_NUMBER() OVER
    (
        PARTITION BY name ORDER BY id
    ) RowNumber
    FROM  dbo.Sectors
)
DELETE  FROM CTE WHERE RowNumber > 1