Delete Remove duplicate in SQL

1) find duplicate

select name,city,COUNT(1)
from mytable
group by name,city
having COUNT(1) > 1

2) remove duplicate - best way

CREATE TABLE #Table1 (col1 int, col2 int)
INSERT INTO #Table1 VALUES (1, 1000)
INSERT INTO #Table1 VALUES (2, 2000)
INSERT INTO #Table1 VALUES (2, 2000)
INSERT INTO #Table1 VALUES (3, 3000)
INSERT INTO #Table1 VALUES (3, 3000)
INSERT INTO #Table1 VALUES (4, 4000)


SELECT * FROM #Table1;

WITH T1 AS (SELECT (ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1)) AS RNum FROM #Table1)
DELETE FROM T1 WHERE RNum IN (SELECT a.RNum FROM T1 AS a, T1 AS b WHERE a.RNum > b.RNum GROUP BY a.RNum)

SELECT * FROM #Table1

DROP TABLE #Table1

3) remove duplicate

SELECT DISTINCT * INTO tab2 FROM tab1 — distinct of all columns
DROP TABLE tab1
EXEC sp_rename ‘tab2′,’tab1′

4) remove duplicate
don't want to tab1
--get distinct duplicate records in #tempTable using option1
--remove all duplicate records from mail table
--insert #tempTable records in to tab1
--drop #tempTable