Using sql server deleting duplicate rows from a table

--Deleting duplicates rows from a table in different way--

select * from t1
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')

-- Query 1 using Group By and Having --

SELECT col1, col2, count(*) FROM t1
GROUP BY col1, col2 HAVING count(*) > 1

-- Query 2 using common type expression CTE

With temptable as   
(  
Select ROW_NUMBER() over (PARTITION BY col1, col2 ORDER BY col1) AS rownumber,* FROM t1
)  
delete from temptable where rownumber > 1  
 
-- Query 3 using sub queries --   

select * from t1 where col1 not in(select min(col1) from t1 group by col1,col2)   
delete from t1 where col1 not in(select min(col1) from t1 group by col1,col2) 


-- Query 4 using row_number() over function --  

Delete t From
(
Select row_number() Over (Partition By col1, col2 Order By col1) As sno, * From t1
) As t
Where sno>1

-- Query 5 using distinct --

SELECT DISTINCT * INTO t2 FROM t1 
DROP TABLE t1
EXEC sp_rename t2,t1