Delete duplicate row from sql table

--create table with Name Employee
CREATE TABLE Employee
(
id INT,
name VARCHAR(50),
salary INT
)

--Insert records in Employee table
INSERT INTO Employee VALUES(1,'ram',3000)
INSERT INTO Employee VALUES(2,'sita',3000)
INSERT INTO Employee VALUES(3,'Jit',600)
INSERT INTO Employee VALUES(4,'gita',30600)
INSERT INTO Employee VALUES(5,'Jai',3000)
INSERT INTO Employee VALUES(9,'ramu',30500)


--Delete duplicate records using CTE
--Delete records using CTE PARTITION BY id(id is column name)
--IF you have to add duplicate check on more column you can add column name
--Ex:- PARTITION BY id,name,salary ORDER BY id,name,salary

WITH EmployeeCTE AS
(
   SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS RowNumber
   FROM Employee
)
DELETE FROM EmployeeCTE WHERE RowNumber > 1

--Delete duplicate records using GROUP BY AND HAVING statement
DELETE  FROM  Employee WHERE id IN (SELECT id FROM Employee GROUP BY id having count(*) >1)

--Delete duplicate records using Temp Variable
SELECT DISTINCT * INTO #tmp FROM Employee
DELETE FROM Employee
INSERT INTO Employee
SELECT * FROM  #tmp DROP TABLE #tmp