DELETE DUPLICATE DATA FROM SQL SERVER CONTAINING NO PRIMARY /PRIMARY KEY.

HOW TO DELETE DUPLICATE VALUES FROM SQL TABLE

Their are severals ways this can be achieved .below are the discussed two ways:

Table structure:
username varchar2
password varchar2

--using CTE COMMON TABLE EXPRESSION and row_number function


WITH CTE AS(
   SELECT username, Password,
       RN = ROW_NUMBER()OVER(PARTITION BY username ORDER BY username)
   FROM dbo.users
)
DELETE FROM CTE WHERE RN > 1

--using auto id column

ALTER TABLE dbo.users ADD AUTOID INT IDENTITY(1,1)

select username,count(username) as counts from users group by username
having count(username)>1


delete from users where AUTOID  not in (select min(AUTOID) from users group by username)


Thus simply done..