Removing Duplicates on a primary key-less table

[rohitnair]

Hi,

I tried to remove duplicates from a table which contains a primary key. It seems there are various ways to tackle this situation. I tried using CTE (Common Table Expressions) and Row_Number function as well as inner join and it worked. It also works with use of distinct records on temporary table. But what if there is no primary key nor any business key which uniquely identifies a record? What if the id is same but other values are different in a record?

id    name     skill

1      vic         DBA

1      vic         DBA

2      Sam       HR

2      Sam       Marketing

Please suggest the optimal solution for the issue mentioned above.

[#2]

@rohitnair your primary key increment auto? Please check the link

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?vi...