Delete Duplicate Data from SQL Table Using Common Table Expression (CTE)

DECLARE @DuplicateRcordTable TABLE (Col1 varchar(10), Col2 varchar(10)) INSERT INTO @DuplicateRcordTable SELECT 'A', 'B'
UNION ALL SELECT 'A', 'B' --duplicate
UNION ALL SELECT 'C', 'C' --duplicate
UNION ALL SELECT 'C', 'C'
UNION ALL SELECT 'C', 'C' --duplicate
UNION ALL SELECT 'D', 'E'
UNION ALL SELECT 'E', 'F'

SELECT * FROM @DuplicateRcordTable;

WITH CTES(COl1,Col2, DuplicateCount)
 AS
 (
            SELECT COl1,Col2, ROW_NUMBER()
              OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
           FROM @DuplicateRcordTable
 )
DELETE FROM CTES WHERE DuplicateCount > 1

SELECT * FROM @DuplicateRcordTable

Remove Duplicate Records from SQL table

This Code will help to remove duplicate records when there is not key field in the table.
/* Create Table Statement */
CREATE TABLE #Employee
(
Name varchar(55) NULL,
Salary decimal(10, 2) NULL,
Designation varchar(20) NULL
 )

 /* Insert Data into the Table*/
 INSERT INTO #Employee VALUES('Lakhan Pal Garg', 10000, 'AST')
 INSERT INTO #Employee VALUES('Lakhan Pal Garg', 10000, 'AST')
 INSERT INTO #Employee VALUES('Amit Tyagi', 10000, 'ITA')
 INSERT INTO #Employee VALUES('Amit Tyagi', 10000, 'ITA')
 INSERT INTO #Employee VALUES('Lakhan Pal Garg', 12000, 'AST')
 INSERT INTO #Employee VALUES('Amit Tyagi', 13000, 'ITA')
 INSERT INTO #Employee VALUES('Sumit Sharma', 15000, 'AST')
 INSERT INTO #Employee VALUES('Sumit Sharma', 15500, 'AST')
 
/* Mark Duplicate Record */
WITH TempEmp (Name,duplicateRecCount)
AS
(
SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary, Designation ORDER BY Name)
AS duplicateRecCount
FROM #Employee
)

/* Now Delete Duplicate Records */
DELETE FROM TempEmp
WHERE duplicateRecCount > 1

/* See updated table data */
Select * from #Employee